{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T23:14:23.553804Z",
     "start_time": "2022-10-26T23:14:23.550255Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/zzachw/Projects/13_pyhealth/PyHealth\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import sys\n",
    "\n",
    "src_path = os.path.abspath(\"../../..\")\n",
    "print(src_path)\n",
    "sys.path.append(src_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T23:14:23.713083Z",
     "start_time": "2022-10-26T23:14:23.703767Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/zzachw/Projects/13_pyhealth/PyHealth/pyhealth/medcode/dev/resource'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "resource_directory = os.path.abspath(\"./resource\")\n",
    "resource_directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T23:14:24.270831Z",
     "start_time": "2022-10-26T23:14:23.839590Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ICD9CM"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:03.345096Z",
     "start_time": "2022-10-10T21:23:03.092585Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>parent_code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>806.11</td>\n",
       "      <td>806.1</td>\n",
       "      <td>Open fracture of C1-C4 level with complete les...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>642.41</td>\n",
       "      <td>642.4</td>\n",
       "      <td>Mild or unspecified pre-eclampsia, delivered, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>647.13</td>\n",
       "      <td>647.1</td>\n",
       "      <td>Gonorrhea of mother, complicating pregnancy, c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>374.21</td>\n",
       "      <td>374.2</td>\n",
       "      <td>Paralytic lagophthalmos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>679.00</td>\n",
       "      <td>679.0</td>\n",
       "      <td>Maternal complications from in utero procedure...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code parent_code                                               name\n",
       "0  806.11       806.1  Open fracture of C1-C4 level with complete les...\n",
       "1  642.41       642.4  Mild or unspecified pre-eclampsia, delivered, ...\n",
       "2  647.13       647.1  Gonorrhea of mother, complicating pregnancy, c...\n",
       "3  374.21       374.2                            Paralytic lagophthalmos\n",
       "4  679.00       679.0  Maternal complications from in utero procedure..."
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\" https://bioportal.bioontology.org/ontologies/ICD9CM \"\"\"\n",
    "\n",
    "raw_data = pd.read_csv(os.path.join(resource_directory, \"raw/ICD9CM.csv\"))\n",
    "raw_data[\"code\"] = raw_data[\"Class ID\"].apply(lambda x: x.split(\"/\")[-1])\n",
    "raw_data[\"vocab\"] = raw_data[\"Class ID\"].apply(lambda x: x.split(\"/\")[-2])\n",
    "raw_data[\"parent_code\"] = raw_data[\"Parents\"].apply(\n",
    "    lambda x: x.split(\"/\")[-1] if not pd.isna(x) else \"\"\n",
    ")\n",
    "raw_data[\"parent_vocab\"] = raw_data[\"Parents\"].apply(\n",
    "    lambda x: x.split(\"/\")[-2] if not pd.isna(x) else \"\"\n",
    ")\n",
    "raw_data[\"name\"] = raw_data[\"Preferred Label\"]\n",
    "# exclude non icd9 codes\n",
    "raw_data = raw_data[raw_data.vocab == \"ICD9CM\"]\n",
    "# exclude icd9proc codes\n",
    "# icd9cm codes: 001-999.99, icd9proc: 00.00-99.99\n",
    "raw_data = raw_data[raw_data.code.apply(lambda x: len(re.split(\"\\.|-\", x)[0]) > 2)]\n",
    "data = raw_data[[\"code\", \"parent_code\", \"name\"]]\n",
    "# exclude non ICD9CM parent code\n",
    "invalid_parents = list(set(data.parent_code.unique()) - set(data.code.unique()))\n",
    "data = data.replace({\"parent_code\": invalid_parents}, \"\")\n",
    "data.to_csv(os.path.join(resource_directory, \"processed/ICD9CM.csv\"), index=False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### to CCSCM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:03.352906Z",
     "start_time": "2022-10-10T21:23:03.348402Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'010.00'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def normalize_icd9cm(code: str):\n",
    "    \"\"\"Normalize ICD9CM code\"\"\"\n",
    "    if code.startswith(\"E\"):\n",
    "        assert len(code) >= 4\n",
    "        if len(code) == 4:\n",
    "            return code\n",
    "        return code[:4] + \".\" + code[4:]\n",
    "    else:\n",
    "        assert len(code) >= 3\n",
    "        if len(code) == 3:\n",
    "            return code\n",
    "        return code[:3] + \".\" + code[3:]\n",
    "\n",
    "\n",
    "normalize_icd9cm(\"01000\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:03.406852Z",
     "start_time": "2022-10-10T21:23:03.354353Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ICD9CM</th>\n",
       "      <th>CCSCM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>010.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010.01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010.02</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010.03</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010.04</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ICD9CM CCSCM\n",
       "0  010.00     1\n",
       "1  010.01     1\n",
       "2  010.02     1\n",
       "3  010.03     1\n",
       "4  010.04     1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"https://www.hcup-us.ahrq.gov/toolssoftware/ccs/ccs.jsp\"\"\"\n",
    "\n",
    "mapping = {}\n",
    "with open(os.path.join(resource_directory, \"raw/$dxref 2015.csv\")) as f:\n",
    "    lines = f.readlines()\n",
    "    for line in lines[3:]:\n",
    "        line = line.split(\",\")\n",
    "        icd9cm_code = line[0].strip(\"'\").strip()\n",
    "        ccscm_code = line[1].strip(\"'\").strip()\n",
    "        assert icd9cm_code not in mapping\n",
    "        mapping[icd9cm_code] = ccscm_code\n",
    "data = {\"ICD9CM\": mapping.keys(), \"CCSCM\": mapping.values()}\n",
    "data = pd.DataFrame.from_dict(data)\n",
    "data.ICD9CM = data.ICD9CM.map(normalize_icd9cm)\n",
    "data.to_csv(\n",
    "    os.path.join(resource_directory, \"processed/ICD9CM_to_CCSCM.csv\"), index=False\n",
    ")\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CCSCM"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T22:31:42.083046Z",
     "start_time": "2022-10-26T22:31:42.069641Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Tuberculosis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>Immunizations and screening for infectious dis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100</td>\n",
       "      <td>Acute myocardial infarction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>Coronary atherosclerosis and other heart disease</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>102</td>\n",
       "      <td>Nonspecific chest pain</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code                                               name\n",
       "0    1                                       Tuberculosis\n",
       "1   10  Immunizations and screening for infectious dis...\n",
       "2  100                        Acute myocardial infarction\n",
       "3  101   Coronary atherosclerosis and other heart disease\n",
       "4  102                             Nonspecific chest pain"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\" https://www.hcup-us.ahrq.gov/toolssoftware/ccs/ccs.jsp \"\"\"\n",
    "\n",
    "data = {}\n",
    "with open(os.path.join(resource_directory, \"raw/dxlabel 2015.csv\")) as f:\n",
    "    lines = f.readlines()\n",
    "    for line in lines[4:]:\n",
    "        line = line.split(\",\")\n",
    "        code = line[0].strip(\"'\").strip()\n",
    "        name = line[1].strip(\"'\").strip()\n",
    "        data[code] = {\"name\": name}\n",
    "data = (\n",
    "    pd.DataFrame.from_dict(data, orient=\"index\")\n",
    "    .reset_index()\n",
    "    .rename(columns={\"index\": \"code\"})\n",
    ")\n",
    "data.to_csv(os.path.join(resource_directory, \"processed/CCSCM.csv\"), index=False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-09T21:30:43.988944Z",
     "start_time": "2022-10-09T21:30:43.982447Z"
    }
   },
   "source": [
    "## ICD9PROC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:03.629787Z",
     "start_time": "2022-10-10T21:23:03.433468Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>parent_code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>94.62</td>\n",
       "      <td>94.6</td>\n",
       "      <td>Alcohol detoxification</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>94.69</td>\n",
       "      <td>94.6</td>\n",
       "      <td>Combined alcohol and drug rehabilitation and d...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>94.6</td>\n",
       "      <td>94</td>\n",
       "      <td>Alcohol and drug rehabilitation and detoxifica...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>94.61</td>\n",
       "      <td>94.6</td>\n",
       "      <td>Alcohol rehabilitation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>94.67</td>\n",
       "      <td>94.6</td>\n",
       "      <td>Combined alcohol and drug rehabilitation</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code parent_code                                               name\n",
       "16  94.62        94.6                             Alcohol detoxification\n",
       "17  94.69        94.6  Combined alcohol and drug rehabilitation and d...\n",
       "18   94.6          94  Alcohol and drug rehabilitation and detoxifica...\n",
       "19  94.61        94.6                             Alcohol rehabilitation\n",
       "20  94.67        94.6           Combined alcohol and drug rehabilitation"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\" https://bioportal.bioontology.org/ontologies/ICD9CM \"\"\"\n",
    "\n",
    "raw_data = pd.read_csv(os.path.join(resource_directory, \"raw/ICD9CM.csv\"))\n",
    "raw_data[\"code\"] = raw_data[\"Class ID\"].apply(lambda x: x.split(\"/\")[-1])\n",
    "raw_data[\"vocab\"] = raw_data[\"Class ID\"].apply(lambda x: x.split(\"/\")[-2])\n",
    "raw_data[\"parent_code\"] = raw_data[\"Parents\"].apply(\n",
    "    lambda x: x.split(\"/\")[-1] if not pd.isna(x) else \"\"\n",
    ")\n",
    "raw_data[\"parent_vocab\"] = raw_data[\"Parents\"].apply(\n",
    "    lambda x: x.split(\"/\")[-2] if not pd.isna(x) else \"\"\n",
    ")\n",
    "raw_data[\"name\"] = raw_data[\"Preferred Label\"]\n",
    "# exclude non icd9 codes\n",
    "raw_data = raw_data[raw_data.vocab == \"ICD9CM\"]\n",
    "# exclude icd9cm codes\n",
    "# icd9cm codes: 001-999.99, icd9proc: 00.00-99.99\n",
    "raw_data = raw_data[raw_data.code.apply(lambda x: len(re.split(\"\\.|-\", x)[0]) <= 2)]\n",
    "data = raw_data[[\"code\", \"parent_code\", \"name\"]]\n",
    "# exclude non icd9proc parent code\n",
    "invalid_parents = list(set(data.parent_code.unique()) - set(data.code.unique()))\n",
    "data = data.replace({\"parent_code\": invalid_parents}, \"\")\n",
    "data.to_csv(os.path.join(resource_directory, \"processed/ICD9PROC.csv\"), index=False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### to CCSPROC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:03.637027Z",
     "start_time": "2022-10-10T21:23:03.633131Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'61.11'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def normalize_icd9proc(code: str):\n",
    "    \"\"\"Normalize ICD9PROC code\"\"\"\n",
    "    assert len(code) >= 2\n",
    "    if len(code) == 2:\n",
    "        return code\n",
    "    return code[:2] + \".\" + code[2:]\n",
    "\n",
    "\n",
    "normalize_icd9proc(\"6111\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:03.661617Z",
     "start_time": "2022-10-10T21:23:03.638368Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ICD9PROC</th>\n",
       "      <th>CCSPROC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01.01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01.09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01.21</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01.22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01.23</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ICD9PROC CCSPROC\n",
       "0    01.01       1\n",
       "1    01.09       1\n",
       "2    01.21       1\n",
       "3    01.22       1\n",
       "4    01.23       1"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"https://www.hcup-us.ahrq.gov/toolssoftware/ccs/ccs.jsp\"\"\"\n",
    "\n",
    "mapping = {}\n",
    "with open(os.path.join(resource_directory, \"raw/$prref 2015.csv\")) as f:\n",
    "    lines = f.readlines()\n",
    "    for line in lines[3:]:\n",
    "        line = line.split(\",\")\n",
    "        icd9proc_code = line[0].strip(\"'\").strip()\n",
    "        ccsproc_code = line[1].strip(\"'\").strip()\n",
    "        assert icd9proc_code not in mapping\n",
    "        mapping[icd9proc_code] = ccsproc_code\n",
    "\n",
    "data = {\"ICD9PROC\": mapping.keys(), \"CCSPROC\": mapping.values()}\n",
    "data = pd.DataFrame.from_dict(data)\n",
    "data.ICD9PROC = data.ICD9PROC.map(normalize_icd9proc)\n",
    "data.to_csv(\n",
    "    os.path.join(resource_directory, \"processed/ICD9PROC_to_CCSPROC.csv\"), index=False\n",
    ")\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CCSPROC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T22:32:49.495213Z",
     "start_time": "2022-10-26T22:32:49.481944Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Incision and excision of CNS\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>Thyroidectomy; partial or complete\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100</td>\n",
       "      <td>Endoscopy and endoscopic biopsy of the urinary...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>Transurethral excision; drainage; or removal u...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>102</td>\n",
       "      <td>Ureteral catheterization\"</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code                                               name\n",
       "0    1                      Incision and excision of CNS\"\n",
       "1   10                Thyroidectomy; partial or complete\"\n",
       "2  100  Endoscopy and endoscopic biopsy of the urinary...\n",
       "3  101  Transurethral excision; drainage; or removal u...\n",
       "4  102                          Ureteral catheterization\""
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\" https://www.hcup-us.ahrq.gov/toolssoftware/ccs/ccs.jsp \"\"\"\n",
    "\n",
    "data = {}\n",
    "with open(os.path.join(resource_directory, \"raw/prlabel 2014.csv\")) as f:\n",
    "    lines = f.readlines()\n",
    "    for line in lines[4:]:\n",
    "        line = line.split(\",\")\n",
    "        code = line[0].strip('\"').strip()\n",
    "        name = line[1].strip('\"').strip()\n",
    "        data[code] = {\"name\": name}\n",
    "data = (\n",
    "    pd.DataFrame.from_dict(data, orient=\"index\")\n",
    "    .reset_index()\n",
    "    .rename(columns={\"index\": \"code\"})\n",
    ")\n",
    "data.to_csv(os.path.join(resource_directory, \"processed/CCSPROC.csv\"), index=False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ICD10CM"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:04.600183Z",
     "start_time": "2022-10-10T21:23:03.679825Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>parent_code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Z62.811</td>\n",
       "      <td>Z62.81</td>\n",
       "      <td>Personal history of psychological abuse in chi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Z01.81</td>\n",
       "      <td>Z01.8</td>\n",
       "      <td>Encounter for preprocedural examinations</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Z01.89</td>\n",
       "      <td>Z01.8</td>\n",
       "      <td>Encounter for other specified special examinat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Z01.8</td>\n",
       "      <td>Z01</td>\n",
       "      <td>Encounter for other specified special examinat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Z01.82</td>\n",
       "      <td>Z01.8</td>\n",
       "      <td>Encounter for allergy testing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      code parent_code                                               name\n",
       "0  Z62.811      Z62.81  Personal history of psychological abuse in chi...\n",
       "1   Z01.81       Z01.8           Encounter for preprocedural examinations\n",
       "2   Z01.89       Z01.8  Encounter for other specified special examinat...\n",
       "3    Z01.8         Z01  Encounter for other specified special examinat...\n",
       "4   Z01.82       Z01.8                      Encounter for allergy testing"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\" https://bioportal.bioontology.org/ontologies/ICD10CM \"\"\"\n",
    "\n",
    "raw_data = pd.read_csv(os.path.join(resource_directory, \"raw/ICD10CM.csv\"))\n",
    "raw_data[\"code\"] = raw_data[\"Class ID\"].apply(lambda x: x.split(\"/\")[-1])\n",
    "raw_data[\"vocab\"] = raw_data[\"Class ID\"].apply(lambda x: x.split(\"/\")[-2])\n",
    "raw_data[\"parent_code\"] = raw_data[\"Parents\"].apply(\n",
    "    lambda x: x.split(\"/\")[-1] if not pd.isna(x) else \"\"\n",
    ")\n",
    "raw_data[\"parent_vocab\"] = raw_data[\"Parents\"].apply(\n",
    "    lambda x: x.split(\"/\")[-2] if not pd.isna(x) else \"\"\n",
    ")\n",
    "raw_data[\"name\"] = raw_data[\"Preferred Label\"]\n",
    "# # exclude non icd10cm codes\n",
    "raw_data = raw_data[raw_data.vocab == \"ICD10CM\"]\n",
    "data = raw_data[[\"code\", \"parent_code\", \"name\"]]\n",
    "# exclude non icd10cm parent code\n",
    "invalid_parents = list(set(data.parent_code.unique()) - set(data.code.unique()))\n",
    "data = data.replace({\"parent_code\": invalid_parents}, \"\")\n",
    "data.to_csv(os.path.join(resource_directory, \"processed/ICD10CM.csv\"), index=False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### to CCSCM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:04.606549Z",
     "start_time": "2022-10-10T21:23:04.601929Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Y92.84'"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def normalize_icd10cm(code: str):\n",
    "    \"\"\"Normalize ICD10CM code\"\"\"\n",
    "    assert len(code) >= 3\n",
    "    if len(code) == 3:\n",
    "        return code\n",
    "    return code[:3] + \".\" + code[3:]\n",
    "\n",
    "\n",
    "normalize_icd10cm(\"Y9284\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:04.843879Z",
     "start_time": "2022-10-10T21:23:04.608327Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ICD10CM</th>\n",
       "      <th>CCSCM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A15.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A15.4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A15.5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A15.6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A15.7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ICD10CM CCSCM\n",
       "0   A15.0     1\n",
       "1   A15.4     1\n",
       "2   A15.5     1\n",
       "3   A15.6     1\n",
       "4   A15.7     1"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"https://www.hcup-us.ahrq.gov/toolssoftware/ccsr/ccsr_archive.jsp#ccsr\"\"\"\n",
    "\n",
    "mapping = {}\n",
    "with open(os.path.join(resource_directory, \"raw/ccs_dx_icd10cm_2019_1.csv\")) as f:\n",
    "    lines = f.readlines()\n",
    "    for line in lines[1:]:\n",
    "        line = line.split(\",\")\n",
    "        icd10cm_code = line[0].strip(\"'\").strip()\n",
    "        ccscm_code = line[1].strip(\"'\").strip()\n",
    "        assert icd10cm_code not in mapping\n",
    "        mapping[icd10cm_code] = ccscm_code\n",
    "data = {\"ICD10CM\": mapping.keys(), \"CCSCM\": mapping.values()}\n",
    "data = pd.DataFrame.from_dict(data)\n",
    "data.ICD10CM = data.ICD10CM.map(normalize_icd10cm)\n",
    "data.to_csv(\n",
    "    os.path.join(resource_directory, \"processed/ICD10CM_to_CCSCM.csv\"), index=False\n",
    ")\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ICD10PROC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:06.833713Z",
     "start_time": "2022-10-10T21:23:04.845143Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>parent_code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0Q894Z</td>\n",
       "      <td>0Q894</td>\n",
       "      <td>Medical and Surgical @ Lower Bones @ Division ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0Q894ZZ</td>\n",
       "      <td>0Q894Z</td>\n",
       "      <td>Division of Left Femoral Shaft, Percutaneous E...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>005W3Z</td>\n",
       "      <td>005W3</td>\n",
       "      <td>Medical and Surgical @ Central Nervous System ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>005W3ZZ</td>\n",
       "      <td>005W3Z</td>\n",
       "      <td>Destruction of Cervical Spinal Cord, Percutane...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2W0MX3</td>\n",
       "      <td>2W0MX</td>\n",
       "      <td>Placement @ Anatomical Regions @ Change @ Lowe...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      code parent_code                                               name\n",
       "0   0Q894Z       0Q894  Medical and Surgical @ Lower Bones @ Division ...\n",
       "1  0Q894ZZ      0Q894Z  Division of Left Femoral Shaft, Percutaneous E...\n",
       "2   005W3Z       005W3  Medical and Surgical @ Central Nervous System ...\n",
       "3  005W3ZZ      005W3Z  Destruction of Cervical Spinal Cord, Percutane...\n",
       "4   2W0MX3       2W0MX  Placement @ Anatomical Regions @ Change @ Lowe..."
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\" https://bioportal.bioontology.org/ontologies/ICD10PCS \"\"\"\n",
    "\n",
    "raw_data = pd.read_csv(os.path.join(resource_directory, \"raw/ICD10PCS.csv\"))\n",
    "raw_data[\"code\"] = raw_data[\"Class ID\"].apply(lambda x: x.split(\"/\")[-1])\n",
    "raw_data[\"vocab\"] = raw_data[\"Class ID\"].apply(lambda x: x.split(\"/\")[-2])\n",
    "raw_data[\"parent_code\"] = raw_data[\"Parents\"].apply(\n",
    "    lambda x: x.split(\"/\")[-1] if not pd.isna(x) else \"\"\n",
    ")\n",
    "raw_data[\"parent_vocab\"] = raw_data[\"Parents\"].apply(\n",
    "    lambda x: x.split(\"/\")[-2] if not pd.isna(x) else \"\"\n",
    ")\n",
    "raw_data[\"name\"] = raw_data[\"Preferred Label\"]\n",
    "# # exclude non icd10proc codes\n",
    "raw_data = raw_data[raw_data.vocab == \"ICD10PCS\"]\n",
    "data = raw_data[[\"code\", \"parent_code\", \"name\"]]\n",
    "# exclude non icd10proc parent code\n",
    "invalid_parents = list(set(data.parent_code.unique()) - set(data.code.unique()))\n",
    "data = data.replace({\"parent_code\": invalid_parents}, \"\")\n",
    "data.to_csv(os.path.join(resource_directory, \"processed/ICD10PROC.csv\"), index=False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### to CCSPROC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:07.064147Z",
     "start_time": "2022-10-10T21:23:06.835407Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ICD10PROC</th>\n",
       "      <th>CCSPROC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00800ZZ</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00803ZZ</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00804ZZ</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00870ZZ</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00873ZZ</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ICD10PROC CCSPROC\n",
       "0   00800ZZ       1\n",
       "1   00803ZZ       1\n",
       "2   00804ZZ       1\n",
       "3   00870ZZ       1\n",
       "4   00873ZZ       1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"https://www.hcup-us.ahrq.gov/toolssoftware/ccs10/ccs10.jsp\"\"\"\n",
    "\n",
    "mapping = {}\n",
    "with open(os.path.join(resource_directory, \"raw/ccs_pr_icd10pcs_2019_1.csv\")) as f:\n",
    "    lines = f.readlines()\n",
    "    for line in lines[1:]:\n",
    "        line = line.split(\",\")\n",
    "        icd10proc_code = line[0].strip(\"'\").strip()\n",
    "        ccsproc_code = line[1].strip(\"'\").strip()\n",
    "        assert icd10proc_code not in mapping\n",
    "        mapping[icd10proc_code] = ccsproc_code\n",
    "data = {\"ICD10PROC\": mapping.keys(), \"CCSPROC\": mapping.values()}\n",
    "data = pd.DataFrame.from_dict(data)\n",
    "data.to_csv(\n",
    "    os.path.join(resource_directory, \"processed/ICD10PROC_to_CCSPROC.csv\"), index=False\n",
    ")\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NDC\n",
    "\n",
    "Download NDC, RxNorm, ATC from https://athena.ohdsi.org/vocabulary/list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:07.067609Z",
     "start_time": "2022-10-10T21:23:07.065456Z"
    }
   },
   "outputs": [],
   "source": [
    "foldername = \"NDC_RxNorm_ATC\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:09.514789Z",
     "start_time": "2022-10-10T21:23:07.068983Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/zzachw/miniconda3/envs/pytorch19/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3441: DtypeWarning: Columns (9) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept_id</th>\n",
       "      <th>concept_name</th>\n",
       "      <th>domain_id</th>\n",
       "      <th>vocabulary_id</th>\n",
       "      <th>concept_class_id</th>\n",
       "      <th>standard_concept</th>\n",
       "      <th>concept_code</th>\n",
       "      <th>valid_start_date</th>\n",
       "      <th>valid_end_date</th>\n",
       "      <th>invalid_reason</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21600001</td>\n",
       "      <td>ALIMENTARY TRACT AND METABOLISM</td>\n",
       "      <td>Drug</td>\n",
       "      <td>ATC</td>\n",
       "      <td>ATC 1st</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21600002</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>Drug</td>\n",
       "      <td>ATC</td>\n",
       "      <td>ATC 2nd</td>\n",
       "      <td>C</td>\n",
       "      <td>A01</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21600003</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>Drug</td>\n",
       "      <td>ATC</td>\n",
       "      <td>ATC 3rd</td>\n",
       "      <td>C</td>\n",
       "      <td>A01A</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21600004</td>\n",
       "      <td>Caries prophylactic agents</td>\n",
       "      <td>Drug</td>\n",
       "      <td>ATC</td>\n",
       "      <td>ATC 4th</td>\n",
       "      <td>C</td>\n",
       "      <td>A01AA</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21600005</td>\n",
       "      <td>sodium fluoride; oral, local oral (caries prop...</td>\n",
       "      <td>Drug</td>\n",
       "      <td>ATC</td>\n",
       "      <td>ATC 5th</td>\n",
       "      <td>C</td>\n",
       "      <td>A01AA01</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  concept_id                                       concept_name domain_id  \\\n",
       "0   21600001                    ALIMENTARY TRACT AND METABOLISM      Drug   \n",
       "1   21600002                        STOMATOLOGICAL PREPARATIONS      Drug   \n",
       "2   21600003                        STOMATOLOGICAL PREPARATIONS      Drug   \n",
       "3   21600004                         Caries prophylactic agents      Drug   \n",
       "4   21600005  sodium fluoride; oral, local oral (caries prop...      Drug   \n",
       "\n",
       "  vocabulary_id concept_class_id standard_concept concept_code  \\\n",
       "0           ATC          ATC 1st                C            A   \n",
       "1           ATC          ATC 2nd                C          A01   \n",
       "2           ATC          ATC 3rd                C         A01A   \n",
       "3           ATC          ATC 4th                C        A01AA   \n",
       "4           ATC          ATC 5th                C      A01AA01   \n",
       "\n",
       "   valid_start_date  valid_end_date invalid_reason  \n",
       "0          19700101        20991231            NaN  \n",
       "1          19700101        20991231            NaN  \n",
       "2          19700101        20991231            NaN  \n",
       "3          19700101        20991231            NaN  \n",
       "4          19700101        20991231            NaN  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concept = pd.read_csv(\n",
    "    os.path.join(resource_directory, f\"raw/{foldername}/CONCEPT.csv\"),\n",
    "    dtype={\n",
    "        \"concept_id\": str,\n",
    "        \"vocabulary_id\": str,\n",
    "        \"concept_class_id\": str,\n",
    "        \"concept_code\": str,\n",
    "    },\n",
    "    sep=\"\\t\",\n",
    ")\n",
    "concept.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:12.295978Z",
     "start_time": "2022-10-10T21:23:09.516435Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept_id_1</th>\n",
       "      <th>concept_id_2</th>\n",
       "      <th>relationship_id</th>\n",
       "      <th>valid_start_date</th>\n",
       "      <th>valid_end_date</th>\n",
       "      <th>invalid_reason</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>45093654</td>\n",
       "      <td>1154196</td>\n",
       "      <td>Maps to</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>44923643</td>\n",
       "      <td>19035209</td>\n",
       "      <td>Maps to</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>45144409</td>\n",
       "      <td>19039121</td>\n",
       "      <td>Maps to</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>44849505</td>\n",
       "      <td>19133296</td>\n",
       "      <td>Maps to</td>\n",
       "      <td>20090101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19058667</td>\n",
       "      <td>19047727</td>\n",
       "      <td>Brand name of</td>\n",
       "      <td>20161007</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4773359</th>\n",
       "      <td>40224172</td>\n",
       "      <td>827884</td>\n",
       "      <td>Mapped from</td>\n",
       "      <td>20210509</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4773360</th>\n",
       "      <td>40224172</td>\n",
       "      <td>829918</td>\n",
       "      <td>Mapped from</td>\n",
       "      <td>20210509</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4773361</th>\n",
       "      <td>40224166</td>\n",
       "      <td>829917</td>\n",
       "      <td>Mapped from</td>\n",
       "      <td>20210509</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4773362</th>\n",
       "      <td>40224166</td>\n",
       "      <td>827886</td>\n",
       "      <td>Mapped from</td>\n",
       "      <td>20210509</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4773363</th>\n",
       "      <td>19087978</td>\n",
       "      <td>45774915</td>\n",
       "      <td>Brand name of</td>\n",
       "      <td>20161007</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4773364 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        concept_id_1 concept_id_2 relationship_id  valid_start_date  \\\n",
       "0           45093654      1154196         Maps to          19700101   \n",
       "1           44923643     19035209         Maps to          19700101   \n",
       "2           45144409     19039121         Maps to          19700101   \n",
       "3           44849505     19133296         Maps to          20090101   \n",
       "4           19058667     19047727   Brand name of          20161007   \n",
       "...              ...          ...             ...               ...   \n",
       "4773359     40224172       827884     Mapped from          20210509   \n",
       "4773360     40224172       829918     Mapped from          20210509   \n",
       "4773361     40224166       829917     Mapped from          20210509   \n",
       "4773362     40224166       827886     Mapped from          20210509   \n",
       "4773363     19087978     45774915   Brand name of          20161007   \n",
       "\n",
       "         valid_end_date  invalid_reason  \n",
       "0              20991231             NaN  \n",
       "1              20991231             NaN  \n",
       "2              20991231             NaN  \n",
       "3              20991231             NaN  \n",
       "4              20991231             NaN  \n",
       "...                 ...             ...  \n",
       "4773359        20991231             NaN  \n",
       "4773360        20991231             NaN  \n",
       "4773361        20991231             NaN  \n",
       "4773362        20991231             NaN  \n",
       "4773363        20991231             NaN  \n",
       "\n",
       "[4773364 rows x 6 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concept_relationship = pd.read_csv(\n",
    "    os.path.join(resource_directory, f\"raw/{foldername}/CONCEPT_RELATIONSHIP.csv\"),\n",
    "    dtype={\"concept_id_1\": str, \"concept_id_2\": str, \"relationship_id\": str},\n",
    "    sep=\"\\t\",\n",
    ")\n",
    "concept_relationship"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:13.512885Z",
     "start_time": "2022-10-10T21:23:12.297650Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ancestor_concept_id</th>\n",
       "      <th>descendant_concept_id</th>\n",
       "      <th>min_levels_of_separation</th>\n",
       "      <th>max_levels_of_separation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>742267</td>\n",
       "      <td>40172924</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>703547</td>\n",
       "      <td>40090686</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>723013</td>\n",
       "      <td>19117335</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>561425</td>\n",
       "      <td>40233203</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>711584</td>\n",
       "      <td>1593324</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072534</th>\n",
       "      <td>45893526</td>\n",
       "      <td>46221584</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072535</th>\n",
       "      <td>45893526</td>\n",
       "      <td>46287704</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072536</th>\n",
       "      <td>45893526</td>\n",
       "      <td>46287705</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072537</th>\n",
       "      <td>45893526</td>\n",
       "      <td>46287706</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072538</th>\n",
       "      <td>45893529</td>\n",
       "      <td>44816302</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3072539 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ancestor_concept_id descendant_concept_id  min_levels_of_separation  \\\n",
       "0                    742267              40172924                         2   \n",
       "1                    703547              40090686                         1   \n",
       "2                    723013              19117335                         1   \n",
       "3                    561425              40233203                         1   \n",
       "4                    711584               1593324                         2   \n",
       "...                     ...                   ...                       ...   \n",
       "3072534            45893526              46221584                         2   \n",
       "3072535            45893526              46287704                         1   \n",
       "3072536            45893526              46287705                         1   \n",
       "3072537            45893526              46287706                         2   \n",
       "3072538            45893529              44816302                         0   \n",
       "\n",
       "         max_levels_of_separation  \n",
       "0                               3  \n",
       "1                               2  \n",
       "2                               1  \n",
       "3                               1  \n",
       "4                               3  \n",
       "...                           ...  \n",
       "3072534                         3  \n",
       "3072535                         1  \n",
       "3072536                         2  \n",
       "3072537                         3  \n",
       "3072538                         0  \n",
       "\n",
       "[3072539 rows x 4 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concept_ancestor = pd.read_csv(\n",
    "    os.path.join(resource_directory, f\"raw/{foldername}/CONCEPT_ANCESTOR.csv\"),\n",
    "    dtype={\"ancestor_concept_id\": str, \"descendant_concept_id\": str},\n",
    "    sep=\"\\t\",\n",
    ")\n",
    "concept_ancestor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:16.410473Z",
     "start_time": "2022-10-10T21:23:13.514472Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/28/p3hkwj8169s656m2d0fhrbym0000gn/T/ipykernel_18599/237910732.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data[\"code\"] = data.concept_code\n",
      "/var/folders/28/p3hkwj8169s656m2d0fhrbym0000gn/T/ipykernel_18599/237910732.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data[\"name\"] = data.concept_name\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8536</th>\n",
       "      <td>00000000001</td>\n",
       "      <td>ferric pyrophosphate citrate 5.44 MG/ML Inject...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8537</th>\n",
       "      <td>00002000001</td>\n",
       "      <td>Ergocalciferol 50000 UNT Oral Capsule [Deltalin]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8538</th>\n",
       "      <td>00002000002</td>\n",
       "      <td>Ergocalciferol 50000 UNT Oral Capsule [Deltalin]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8539</th>\n",
       "      <td>00002001402</td>\n",
       "      <td>Flurandrenolide 0.004 MG/SQCM Medicated Tape [...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8540</th>\n",
       "      <td>00002001801</td>\n",
       "      <td>Capreomycin 500 MG/ML Injectable Solution [Cap...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             code                                               name\n",
       "8536  00000000001  ferric pyrophosphate citrate 5.44 MG/ML Inject...\n",
       "8537  00002000001   Ergocalciferol 50000 UNT Oral Capsule [Deltalin]\n",
       "8538  00002000002   Ergocalciferol 50000 UNT Oral Capsule [Deltalin]\n",
       "8539  00002001402  Flurandrenolide 0.004 MG/SQCM Medicated Tape [...\n",
       "8540  00002001801  Capreomycin 500 MG/ML Injectable Solution [Cap..."
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = concept[concept.vocabulary_id == \"NDC\"]\n",
    "data[\"code\"] = data.concept_code\n",
    "data[\"name\"] = data.concept_name\n",
    "data = data[[\"code\", \"name\"]]\n",
    "data = data.drop_duplicates().dropna()\n",
    "data.to_csv(os.path.join(resource_directory, \"processed/NDC.csv\"), index=False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### to RxNorm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:20.310668Z",
     "start_time": "2022-10-10T21:23:16.412342Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NDC</th>\n",
       "      <th>RxNorm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00002000001</td>\n",
       "      <td>1367414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00002000002</td>\n",
       "      <td>1367414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00002001402</td>\n",
       "      <td>797697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000235870</td>\n",
       "      <td>797697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00023587024</td>\n",
       "      <td>797697</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           NDC   RxNorm\n",
       "0  00002000001  1367414\n",
       "1  00002000002  1367414\n",
       "2  00002001402   797697\n",
       "3    000235870   797697\n",
       "4  00023587024   797697"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concept_ndc = concept[concept.vocabulary_id == \"NDC\"]\n",
    "concept_relationship_maps_to = concept_relationship[\n",
    "    concept_relationship.relationship_id == \"Maps to\"\n",
    "]\n",
    "concept_relationship_maps_to = concept_relationship_maps_to[\n",
    "    pd.isna(concept_relationship_maps_to.invalid_reason)\n",
    "]\n",
    "concept_rxnorm = concept[concept.vocabulary_id == \"RxNorm\"]\n",
    "\n",
    "ndc_rxnorm = concept_ndc.merge(\n",
    "    concept_relationship_maps_to,\n",
    "    left_on=\"concept_id\",\n",
    "    right_on=\"concept_id_1\",\n",
    "    how=\"inner\",\n",
    "    suffixes=(\"_ndc\", \"_r\"),\n",
    ")\n",
    "ndc_rxnorm = ndc_rxnorm.merge(\n",
    "    concept_rxnorm,\n",
    "    left_on=\"concept_id_2\",\n",
    "    right_on=\"concept_id\",\n",
    "    how=\"inner\",\n",
    "    suffixes=(\"_ndc\", \"_rxnorm\"),\n",
    ")\n",
    "ndc_rxnorm = ndc_rxnorm[\n",
    "    [\n",
    "        \"concept_id_ndc\",\n",
    "        \"concept_name_ndc\",\n",
    "        \"concept_code_ndc\",\n",
    "        \"concept_id_rxnorm\",\n",
    "        \"concept_name_rxnorm\",\n",
    "        \"concept_code_rxnorm\",\n",
    "    ]\n",
    "]\n",
    "\n",
    "data = pd.DataFrame()\n",
    "data[\"NDC\"] = ndc_rxnorm.concept_code_ndc\n",
    "data[\"RxNorm\"] = ndc_rxnorm.concept_code_rxnorm\n",
    "data.to_csv(\n",
    "    os.path.join(resource_directory, \"processed/NDC_to_RxNorm.csv\"), index=False\n",
    ")\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### to ATC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:28.546849Z",
     "start_time": "2022-10-10T21:23:20.313147Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NDC</th>\n",
       "      <th>ATC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00002000001</td>\n",
       "      <td>A11CC01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00002000002</td>\n",
       "      <td>A11CC01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00002026002</td>\n",
       "      <td>A11CC01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00115014000</td>\n",
       "      <td>A11CC01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00115014001</td>\n",
       "      <td>A11CC01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           NDC      ATC\n",
       "0  00002000001  A11CC01\n",
       "1  00002000002  A11CC01\n",
       "2  00002026002  A11CC01\n",
       "3  00115014000  A11CC01\n",
       "4  00115014001  A11CC01"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first convert NDC to RxNorm\n",
    "concept_ndc = concept[concept.vocabulary_id == \"NDC\"]\n",
    "concept_relationship_maps_to = concept_relationship[\n",
    "    concept_relationship.relationship_id == \"Maps to\"\n",
    "]\n",
    "concept_relationship_maps_to = concept_relationship_maps_to[\n",
    "    pd.isna(concept_relationship_maps_to.invalid_reason)\n",
    "]\n",
    "concept_rxnorm = concept[concept.vocabulary_id == \"RxNorm\"]\n",
    "\n",
    "ndc_rxnorm = concept_ndc.merge(\n",
    "    concept_relationship_maps_to,\n",
    "    left_on=\"concept_id\",\n",
    "    right_on=\"concept_id_1\",\n",
    "    how=\"inner\",\n",
    "    suffixes=(\"_ndc\", \"_r\"),\n",
    ")\n",
    "ndc_rxnorm = ndc_rxnorm.merge(\n",
    "    concept_rxnorm,\n",
    "    left_on=\"concept_id_2\",\n",
    "    right_on=\"concept_id\",\n",
    "    how=\"inner\",\n",
    "    suffixes=(\"_ndc\", \"_rxnorm\"),\n",
    ")\n",
    "ndc_rxnorm = ndc_rxnorm[\n",
    "    [\n",
    "        \"concept_id_ndc\",\n",
    "        \"concept_name_ndc\",\n",
    "        \"concept_code_ndc\",\n",
    "        \"concept_id_rxnorm\",\n",
    "        \"concept_name_rxnorm\",\n",
    "        \"concept_code_rxnorm\",\n",
    "    ]\n",
    "]\n",
    "\n",
    "# then convert RxNorm to ATC5\n",
    "concept_atc5 = concept[concept.vocabulary_id == \"ATC\"]\n",
    "concept_atc5 = concept_atc5[concept_atc5.concept_class_id == \"ATC 5th\"]\n",
    "\n",
    "ndc_rxnorm_atc5 = ndc_rxnorm.merge(\n",
    "    concept_ancestor,\n",
    "    left_on=\"concept_id_rxnorm\",\n",
    "    right_on=\"descendant_concept_id\",\n",
    "    how=\"inner\",\n",
    "    suffixes=(\"_rxnorm\", \"_r\"),\n",
    ")\n",
    "ndc_rxnorm_atc5 = ndc_rxnorm_atc5.merge(\n",
    "    concept_atc5,\n",
    "    left_on=\"ancestor_concept_id\",\n",
    "    right_on=\"concept_id\",\n",
    "    how=\"inner\",\n",
    "    suffixes=(\"_rxnorm\", \"_atc\"),\n",
    ")\n",
    "ndc_rxnorm_atc5 = ndc_rxnorm_atc5.rename(\n",
    "    columns={\n",
    "        \"concept_id\": \"concept_id_atc5\",\n",
    "        \"concept_name\": \"concept_name_atc5\",\n",
    "        \"concept_code\": \"concept_code_atc5\",\n",
    "    }\n",
    ")\n",
    "ndc_rxnorm_atc5 = ndc_rxnorm_atc5[\n",
    "    [\n",
    "        \"concept_id_ndc\",\n",
    "        \"concept_name_ndc\",\n",
    "        \"concept_code_ndc\",\n",
    "        \"concept_id_rxnorm\",\n",
    "        \"concept_name_rxnorm\",\n",
    "        \"concept_code_rxnorm\",\n",
    "        \"concept_id_atc5\",\n",
    "        \"concept_name_atc5\",\n",
    "        \"concept_code_atc5\",\n",
    "    ]\n",
    "]\n",
    "\n",
    "data = pd.DataFrame()\n",
    "data[\"NDC\"] = ndc_rxnorm_atc5.concept_code_ndc\n",
    "data[\"ATC\"] = ndc_rxnorm_atc5.concept_code_atc5\n",
    "data.to_csv(os.path.join(resource_directory, \"processed/NDC_to_ATC.csv\"), index=False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RxNorm\n",
    "\n",
    "Download NDC, RxNorm, ATC from https://athena.ohdsi.org/vocabulary/list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:28.551877Z",
     "start_time": "2022-10-10T21:23:28.549290Z"
    }
   },
   "outputs": [],
   "source": [
    "foldername = \"NDC_RxNorm_ATC\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:30.781332Z",
     "start_time": "2022-10-10T21:23:28.553843Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/zzachw/miniconda3/envs/pytorch19/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3441: DtypeWarning: Columns (9) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept_id</th>\n",
       "      <th>concept_name</th>\n",
       "      <th>domain_id</th>\n",
       "      <th>vocabulary_id</th>\n",
       "      <th>concept_class_id</th>\n",
       "      <th>standard_concept</th>\n",
       "      <th>concept_code</th>\n",
       "      <th>valid_start_date</th>\n",
       "      <th>valid_end_date</th>\n",
       "      <th>invalid_reason</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21600001</td>\n",
       "      <td>ALIMENTARY TRACT AND METABOLISM</td>\n",
       "      <td>Drug</td>\n",
       "      <td>ATC</td>\n",
       "      <td>ATC 1st</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21600002</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>Drug</td>\n",
       "      <td>ATC</td>\n",
       "      <td>ATC 2nd</td>\n",
       "      <td>C</td>\n",
       "      <td>A01</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21600003</td>\n",
       "      <td>STOMATOLOGICAL PREPARATIONS</td>\n",
       "      <td>Drug</td>\n",
       "      <td>ATC</td>\n",
       "      <td>ATC 3rd</td>\n",
       "      <td>C</td>\n",
       "      <td>A01A</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21600004</td>\n",
       "      <td>Caries prophylactic agents</td>\n",
       "      <td>Drug</td>\n",
       "      <td>ATC</td>\n",
       "      <td>ATC 4th</td>\n",
       "      <td>C</td>\n",
       "      <td>A01AA</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21600005</td>\n",
       "      <td>sodium fluoride; oral, local oral (caries prop...</td>\n",
       "      <td>Drug</td>\n",
       "      <td>ATC</td>\n",
       "      <td>ATC 5th</td>\n",
       "      <td>C</td>\n",
       "      <td>A01AA01</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  concept_id                                       concept_name domain_id  \\\n",
       "0   21600001                    ALIMENTARY TRACT AND METABOLISM      Drug   \n",
       "1   21600002                        STOMATOLOGICAL PREPARATIONS      Drug   \n",
       "2   21600003                        STOMATOLOGICAL PREPARATIONS      Drug   \n",
       "3   21600004                         Caries prophylactic agents      Drug   \n",
       "4   21600005  sodium fluoride; oral, local oral (caries prop...      Drug   \n",
       "\n",
       "  vocabulary_id concept_class_id standard_concept concept_code  \\\n",
       "0           ATC          ATC 1st                C            A   \n",
       "1           ATC          ATC 2nd                C          A01   \n",
       "2           ATC          ATC 3rd                C         A01A   \n",
       "3           ATC          ATC 4th                C        A01AA   \n",
       "4           ATC          ATC 5th                C      A01AA01   \n",
       "\n",
       "   valid_start_date  valid_end_date invalid_reason  \n",
       "0          19700101        20991231            NaN  \n",
       "1          19700101        20991231            NaN  \n",
       "2          19700101        20991231            NaN  \n",
       "3          19700101        20991231            NaN  \n",
       "4          19700101        20991231            NaN  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concept = pd.read_csv(\n",
    "    os.path.join(resource_directory, f\"raw/{foldername}/CONCEPT.csv\"),\n",
    "    dtype={\n",
    "        \"concept_id\": str,\n",
    "        \"vocabulary_id\": str,\n",
    "        \"concept_class_id\": str,\n",
    "        \"concept_code\": str,\n",
    "    },\n",
    "    sep=\"\\t\",\n",
    ")\n",
    "concept.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:33.404264Z",
     "start_time": "2022-10-10T21:23:30.783058Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept_id_1</th>\n",
       "      <th>concept_id_2</th>\n",
       "      <th>relationship_id</th>\n",
       "      <th>valid_start_date</th>\n",
       "      <th>valid_end_date</th>\n",
       "      <th>invalid_reason</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>45093654</td>\n",
       "      <td>1154196</td>\n",
       "      <td>Maps to</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>44923643</td>\n",
       "      <td>19035209</td>\n",
       "      <td>Maps to</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>45144409</td>\n",
       "      <td>19039121</td>\n",
       "      <td>Maps to</td>\n",
       "      <td>19700101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>44849505</td>\n",
       "      <td>19133296</td>\n",
       "      <td>Maps to</td>\n",
       "      <td>20090101</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19058667</td>\n",
       "      <td>19047727</td>\n",
       "      <td>Brand name of</td>\n",
       "      <td>20161007</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4773359</th>\n",
       "      <td>40224172</td>\n",
       "      <td>827884</td>\n",
       "      <td>Mapped from</td>\n",
       "      <td>20210509</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4773360</th>\n",
       "      <td>40224172</td>\n",
       "      <td>829918</td>\n",
       "      <td>Mapped from</td>\n",
       "      <td>20210509</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4773361</th>\n",
       "      <td>40224166</td>\n",
       "      <td>829917</td>\n",
       "      <td>Mapped from</td>\n",
       "      <td>20210509</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4773362</th>\n",
       "      <td>40224166</td>\n",
       "      <td>827886</td>\n",
       "      <td>Mapped from</td>\n",
       "      <td>20210509</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4773363</th>\n",
       "      <td>19087978</td>\n",
       "      <td>45774915</td>\n",
       "      <td>Brand name of</td>\n",
       "      <td>20161007</td>\n",
       "      <td>20991231</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4773364 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        concept_id_1 concept_id_2 relationship_id  valid_start_date  \\\n",
       "0           45093654      1154196         Maps to          19700101   \n",
       "1           44923643     19035209         Maps to          19700101   \n",
       "2           45144409     19039121         Maps to          19700101   \n",
       "3           44849505     19133296         Maps to          20090101   \n",
       "4           19058667     19047727   Brand name of          20161007   \n",
       "...              ...          ...             ...               ...   \n",
       "4773359     40224172       827884     Mapped from          20210509   \n",
       "4773360     40224172       829918     Mapped from          20210509   \n",
       "4773361     40224166       829917     Mapped from          20210509   \n",
       "4773362     40224166       827886     Mapped from          20210509   \n",
       "4773363     19087978     45774915   Brand name of          20161007   \n",
       "\n",
       "         valid_end_date  invalid_reason  \n",
       "0              20991231             NaN  \n",
       "1              20991231             NaN  \n",
       "2              20991231             NaN  \n",
       "3              20991231             NaN  \n",
       "4              20991231             NaN  \n",
       "...                 ...             ...  \n",
       "4773359        20991231             NaN  \n",
       "4773360        20991231             NaN  \n",
       "4773361        20991231             NaN  \n",
       "4773362        20991231             NaN  \n",
       "4773363        20991231             NaN  \n",
       "\n",
       "[4773364 rows x 6 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concept_relationship = pd.read_csv(\n",
    "    os.path.join(resource_directory, f\"raw/{foldername}/CONCEPT_RELATIONSHIP.csv\"),\n",
    "    dtype={\"concept_id_1\": str, \"concept_id_2\": str, \"relationship_id\": str},\n",
    "    sep=\"\\t\",\n",
    ")\n",
    "concept_relationship"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:34.597487Z",
     "start_time": "2022-10-10T21:23:33.410216Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ancestor_concept_id</th>\n",
       "      <th>descendant_concept_id</th>\n",
       "      <th>min_levels_of_separation</th>\n",
       "      <th>max_levels_of_separation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>742267</td>\n",
       "      <td>40172924</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>703547</td>\n",
       "      <td>40090686</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>723013</td>\n",
       "      <td>19117335</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>561425</td>\n",
       "      <td>40233203</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>711584</td>\n",
       "      <td>1593324</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072534</th>\n",
       "      <td>45893526</td>\n",
       "      <td>46221584</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072535</th>\n",
       "      <td>45893526</td>\n",
       "      <td>46287704</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072536</th>\n",
       "      <td>45893526</td>\n",
       "      <td>46287705</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072537</th>\n",
       "      <td>45893526</td>\n",
       "      <td>46287706</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3072538</th>\n",
       "      <td>45893529</td>\n",
       "      <td>44816302</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3072539 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ancestor_concept_id descendant_concept_id  min_levels_of_separation  \\\n",
       "0                    742267              40172924                         2   \n",
       "1                    703547              40090686                         1   \n",
       "2                    723013              19117335                         1   \n",
       "3                    561425              40233203                         1   \n",
       "4                    711584               1593324                         2   \n",
       "...                     ...                   ...                       ...   \n",
       "3072534            45893526              46221584                         2   \n",
       "3072535            45893526              46287704                         1   \n",
       "3072536            45893526              46287705                         1   \n",
       "3072537            45893526              46287706                         2   \n",
       "3072538            45893529              44816302                         0   \n",
       "\n",
       "         max_levels_of_separation  \n",
       "0                               3  \n",
       "1                               2  \n",
       "2                               1  \n",
       "3                               1  \n",
       "4                               3  \n",
       "...                           ...  \n",
       "3072534                         3  \n",
       "3072535                         1  \n",
       "3072536                         2  \n",
       "3072537                         3  \n",
       "3072538                         0  \n",
       "\n",
       "[3072539 rows x 4 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concept_ancestor = pd.read_csv(\n",
    "    os.path.join(resource_directory, f\"raw/{foldername}/CONCEPT_ANCESTOR.csv\"),\n",
    "    dtype={\"ancestor_concept_id\": str, \"descendant_concept_id\": str},\n",
    "    sep=\"\\t\",\n",
    ")\n",
    "concept_ancestor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:35.442351Z",
     "start_time": "2022-10-10T21:23:34.599310Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/28/p3hkwj8169s656m2d0fhrbym0000gn/T/ipykernel_18599/2609616381.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data[\"code\"] = data.concept_code\n",
      "/var/folders/28/p3hkwj8169s656m2d0fhrbym0000gn/T/ipykernel_18599/2609616381.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data[\"name\"] = data.concept_name\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1299328</th>\n",
       "      <td>1000000</td>\n",
       "      <td>amlodipine 5 MG / hydrochlorothiazide 12.5 MG ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1299329</th>\n",
       "      <td>1000001</td>\n",
       "      <td>amlodipine 5 MG / hydrochlorothiazide 25 MG / ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1299330</th>\n",
       "      <td>1000002</td>\n",
       "      <td>Tribenzor 40/5/25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1299331</th>\n",
       "      <td>1000003</td>\n",
       "      <td>amlodipine 5 MG / hydrochlorothiazide 25 MG / ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1299332</th>\n",
       "      <td>1000004</td>\n",
       "      <td>Amlodipine / Hydrochlorothiazide / Olmesartan ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            code                                               name\n",
       "1299328  1000000  amlodipine 5 MG / hydrochlorothiazide 12.5 MG ...\n",
       "1299329  1000001  amlodipine 5 MG / hydrochlorothiazide 25 MG / ...\n",
       "1299330  1000002                                  Tribenzor 40/5/25\n",
       "1299331  1000003  amlodipine 5 MG / hydrochlorothiazide 25 MG / ...\n",
       "1299332  1000004  Amlodipine / Hydrochlorothiazide / Olmesartan ..."
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = concept[concept.vocabulary_id == \"RxNorm\"]\n",
    "data[\"code\"] = data.concept_code\n",
    "data[\"name\"] = data.concept_name\n",
    "data = data[[\"code\", \"name\"]]\n",
    "data = data.drop_duplicates().dropna()\n",
    "data.to_csv(os.path.join(resource_directory, \"processed/RxNorm.csv\"), index=False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### to ATC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:39.248186Z",
     "start_time": "2022-10-10T21:23:35.443530Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RxNorm</th>\n",
       "      <th>ATC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000000</td>\n",
       "      <td>C09DA08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000001</td>\n",
       "      <td>C09DA08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000003</td>\n",
       "      <td>C09DA08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000005</td>\n",
       "      <td>C09DA08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10763</td>\n",
       "      <td>C09DA08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    RxNorm      ATC\n",
       "0  1000000  C09DA08\n",
       "1  1000001  C09DA08\n",
       "2  1000003  C09DA08\n",
       "3  1000005  C09DA08\n",
       "4    10763  C09DA08"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concept_rxnorm = concept[concept.vocabulary_id == \"RxNorm\"]\n",
    "concept_atc5 = concept[concept.vocabulary_id == \"ATC\"]\n",
    "concept_atc5 = concept_atc5[concept_atc5.concept_class_id == \"ATC 5th\"]\n",
    "\n",
    "rxnorm_atc5 = concept_rxnorm.merge(\n",
    "    concept_ancestor,\n",
    "    left_on=\"concept_id\",\n",
    "    right_on=\"descendant_concept_id\",\n",
    "    how=\"inner\",\n",
    "    suffixes=(\"_rxnorm\", \"_r\"),\n",
    ")\n",
    "rxnorm_atc5 = rxnorm_atc5.merge(\n",
    "    concept_atc5,\n",
    "    left_on=\"ancestor_concept_id\",\n",
    "    right_on=\"concept_id\",\n",
    "    how=\"inner\",\n",
    "    suffixes=(\"_rxnorm\", \"_atc\"),\n",
    ")\n",
    "rxnorm_atc5 = rxnorm_atc5[\n",
    "    [\n",
    "        \"concept_id_rxnorm\",\n",
    "        \"concept_name_rxnorm\",\n",
    "        \"concept_code_rxnorm\",\n",
    "        \"concept_id_atc\",\n",
    "        \"concept_name_atc\",\n",
    "        \"concept_code_atc\",\n",
    "    ]\n",
    "]\n",
    "\n",
    "data = pd.DataFrame()\n",
    "data[\"RxNorm\"] = rxnorm_atc5.concept_code_rxnorm\n",
    "data[\"ATC\"] = rxnorm_atc5.concept_code_atc\n",
    "data.to_csv(\n",
    "    os.path.join(resource_directory, \"processed/RxNorm_to_ATC.csv\"), index=False\n",
    ")\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ATC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:39.255574Z",
     "start_time": "2022-10-10T21:23:39.250541Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'V10XA'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_atc_parent(code: str):\n",
    "    \"\"\"Get parent code of ATC code\"\"\"\n",
    "    if len(code) == 7:\n",
    "        return code[:5]\n",
    "    elif len(code) == 5:\n",
    "        return code[:4]\n",
    "    elif len(code) == 4:\n",
    "        return code[:3]\n",
    "    elif len(code) == 3:\n",
    "        return code[:1]\n",
    "    else:\n",
    "        return \"\"\n",
    "\n",
    "\n",
    "get_atc_parent(\"V10XA53\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:39.296906Z",
     "start_time": "2022-10-10T21:23:39.256844Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>parent_code</th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5732</th>\n",
       "      <td>A</td>\n",
       "      <td></td>\n",
       "      <td>ALIMENTARY TRACT AND METABOLISM DRUGS</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5993</th>\n",
       "      <td>B</td>\n",
       "      <td></td>\n",
       "      <td>BLOOD AND BLOOD FORMING ORGAN DRUGS</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5477</th>\n",
       "      <td>C</td>\n",
       "      <td></td>\n",
       "      <td>CARDIOVASCULAR SYSTEM DRUGS</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2143</th>\n",
       "      <td>D</td>\n",
       "      <td></td>\n",
       "      <td>DERMATOLOGICALS</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6393</th>\n",
       "      <td>G</td>\n",
       "      <td></td>\n",
       "      <td>GENITO URINARY SYSTEM AND SEX HORMONES</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code parent_code                                    name  level\n",
       "5732    A               ALIMENTARY TRACT AND METABOLISM DRUGS    1.0\n",
       "5993    B                 BLOOD AND BLOOD FORMING ORGAN DRUGS    1.0\n",
       "5477    C                         CARDIOVASCULAR SYSTEM DRUGS    1.0\n",
       "2143    D                                     DERMATOLOGICALS    1.0\n",
       "6393    G              GENITO URINARY SYSTEM AND SEX HORMONES    1.0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"https://bioportal.bioontology.org/ontologies/ATC\"\"\"\n",
    "\n",
    "atc = pd.read_csv(os.path.join(resource_directory, \"raw/ATC.csv\"))\n",
    "atc = atc[[\"Class ID\", \"Preferred Label\", \"ATC LEVEL\"]]\n",
    "atc[\"Class ID\"] = atc[\"Class ID\"].apply(lambda x: x.split(\"/\")[-1])\n",
    "atc = atc.dropna()\n",
    "atc = atc.drop_duplicates()\n",
    "atc[\"parent_code\"] = atc[\"Class ID\"].map(get_atc_parent)\n",
    "atc = atc.sort_values(by=[\"ATC LEVEL\", \"Class ID\"])\n",
    "atc.columns = [\"code\", \"name\", \"level\", \"parent_code\"]\n",
    "atc = atc[[\"code\", \"parent_code\", \"name\", \"level\"]]\n",
    "atc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "additional info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:39.646645Z",
     "start_time": "2022-10-10T21:23:39.298138Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>parent_code</th>\n",
       "      <th>name</th>\n",
       "      <th>level</th>\n",
       "      <th>description</th>\n",
       "      <th>indication</th>\n",
       "      <th>smiles</th>\n",
       "      <th>drugbank_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td></td>\n",
       "      <td>ALIMENTARY TRACT AND METABOLISM DRUGS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td></td>\n",
       "      <td>BLOOD AND BLOOD FORMING ORGAN DRUGS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>C</td>\n",
       "      <td></td>\n",
       "      <td>CARDIOVASCULAR SYSTEM DRUGS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td></td>\n",
       "      <td>DERMATOLOGICALS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>G</td>\n",
       "      <td></td>\n",
       "      <td>GENITO URINARY SYSTEM AND SEX HORMONES</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code parent_code                                    name  level description  \\\n",
       "0    A               ALIMENTARY TRACT AND METABOLISM DRUGS    1.0         NaN   \n",
       "1    B                 BLOOD AND BLOOD FORMING ORGAN DRUGS    1.0         NaN   \n",
       "2    C                         CARDIOVASCULAR SYSTEM DRUGS    1.0         NaN   \n",
       "3    D                                     DERMATOLOGICALS    1.0         NaN   \n",
       "4    G              GENITO URINARY SYSTEM AND SEX HORMONES    1.0         NaN   \n",
       "\n",
       "  indication smiles drugbank_id  \n",
       "0        NaN    NaN         NaN  \n",
       "1        NaN    NaN         NaN  \n",
       "2        NaN    NaN         NaN  \n",
       "3        NaN    NaN         NaN  \n",
       "4        NaN    NaN         NaN  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"https://go.drugbank.com/releases/latest\"\"\"\n",
    "\n",
    "drugbank = pd.read_csv(\n",
    "    os.path.join(resource_directory, \"raw/drugs_info_5_1_8.csv\").replace(\"\\\\\", \"/\")\n",
    ")\n",
    "drugbank = drugbank.fillna(\"\")\n",
    "drugbank.atc_codes = drugbank.atc_codes.apply(lambda x: x.split(\"|\"))\n",
    "drugbank = drugbank.explode(\"atc_codes\")\n",
    "drugbank = drugbank[[\"drugbank_id\", \"description\", \"indication\", \"atc_codes\", \"smiles\"]]\n",
    "atc = atc.merge(drugbank, left_on=\"code\", right_on=\"atc_codes\", how=\"left\")\n",
    "atc = atc[\n",
    "    [\n",
    "        \"code\",\n",
    "        \"parent_code\",\n",
    "        \"name\",\n",
    "        \"level\",\n",
    "        \"description\",\n",
    "        \"indication\",\n",
    "        \"smiles\",\n",
    "        \"drugbank_id\",\n",
    "    ]\n",
    "]\n",
    "atc.to_csv(os.path.join(resource_directory, \"processed/ATC.csv\"), index=False)\n",
    "atc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### to ICD9CM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-10T21:23:39.766060Z",
     "start_time": "2022-10-10T21:23:39.647949Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ATC</th>\n",
       "      <th>ICD9CM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>R05CB05</td>\n",
       "      <td>595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>R05CB05</td>\n",
       "      <td>977.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>R05CB05</td>\n",
       "      <td>595.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>R05CB05</td>\n",
       "      <td>459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>V03AF01</td>\n",
       "      <td>595</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ATC ICD9CM\n",
       "0  R05CB05    595\n",
       "1  R05CB05  977.9\n",
       "2  R05CB05  595.9\n",
       "3  R05CB05    459\n",
       "4  V03AF01    595"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"https://www.vumc.org/cpm/cpm-blog/medi-ensemble-medication-indication-resource-0\"\"\"\n",
    "\n",
    "medi = pd.read_csv(os.path.join(resource_directory, \"raw/MEDI_11242015.csv\"))\n",
    "medi = medi[medi.HSP == 1]\n",
    "medi.CODE = medi.CODE.apply(lambda x: x.split(\"|\"))\n",
    "medi = medi.explode(\"CODE\")\n",
    "medi = medi.dropna().drop_duplicates().reset_index(drop=True)\n",
    "medi = medi[[\"ATC\", \"CODE\"]]\n",
    "medi.columns = [\"ATC\", \"ICD9CM\"]\n",
    "medi.to_csv(\n",
    "    os.path.join(resource_directory, \"processed/ATC_to_ICD9CM.csv\"), index=False\n",
    ")\n",
    "medi.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T23:14:13.587864Z",
     "start_time": "2022-10-26T23:14:13.584254Z"
    }
   },
   "source": [
    "### DDI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T23:17:19.249010Z",
     "start_time": "2022-10-26T23:17:17.406226Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STITCH 1</th>\n",
       "      <th>STITCH 2</th>\n",
       "      <th>Polypharmacy Side Effect</th>\n",
       "      <th>Side Effect Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CID000002173</td>\n",
       "      <td>CID000003345</td>\n",
       "      <td>C0151714</td>\n",
       "      <td>hypermagnesemia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CID000002173</td>\n",
       "      <td>CID000003345</td>\n",
       "      <td>C0035344</td>\n",
       "      <td>retinopathy of prematurity</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CID000002173</td>\n",
       "      <td>CID000003345</td>\n",
       "      <td>C0004144</td>\n",
       "      <td>atelectasis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CID000002173</td>\n",
       "      <td>CID000003345</td>\n",
       "      <td>C0002063</td>\n",
       "      <td>alkalosis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CID000002173</td>\n",
       "      <td>CID000003345</td>\n",
       "      <td>C0004604</td>\n",
       "      <td>Back Ache</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       STITCH 1      STITCH 2 Polypharmacy Side Effect  \\\n",
       "0  CID000002173  CID000003345                 C0151714   \n",
       "1  CID000002173  CID000003345                 C0035344   \n",
       "2  CID000002173  CID000003345                 C0004144   \n",
       "3  CID000002173  CID000003345                 C0002063   \n",
       "4  CID000002173  CID000003345                 C0004604   \n",
       "\n",
       "             Side Effect Name  \n",
       "0             hypermagnesemia  \n",
       "1  retinopathy of prematurity  \n",
       "2                 atelectasis  \n",
       "3                   alkalosis  \n",
       "4                   Back Ache  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"https://snap.stanford.edu/biodata/datasets/10017/10017-ChChSe-Decagon.html\"\"\"\n",
    "ddi = pd.read_csv(\n",
    "    os.path.join(resource_directory, \"raw/DDI/ChChSe-Decagon_polypharmacy.csv\")\n",
    ")\n",
    "ddi = ddi.rename(columns={\"# STITCH 1\": \"STITCH 1\"})\n",
    "ddi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T23:17:19.297804Z",
     "start_time": "2022-10-26T23:17:19.251021Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CID000000085': ['A16AA01'],\n",
       " 'CID000000119': ['N03AG03', 'L03AA03'],\n",
       " 'CID000000137': ['L01XD04'],\n",
       " 'CID000000143': ['V03AF06', 'V03AF04', 'V03AF03'],\n",
       " 'CID000000158': ['G02AD02'],\n",
       " 'CID000000159': ['B01AC09'],\n",
       " 'CID000000160': ['G02AD01'],\n",
       " 'CID000000175': ['S02AA10', 'G01AD02'],\n",
       " 'CID000000187': ['S01EB09'],\n",
       " 'CID000000191': ['C01EB10', 'J05AB03', 'S01AD06'],\n",
       " 'CID000000206': ['B05CB01',\n",
       "  'B05CX01',\n",
       "  'B05XA03',\n",
       "  'A12CA01',\n",
       "  'B05XA07',\n",
       "  'B05XA01',\n",
       "  'A12BA01',\n",
       "  'A12AA07',\n",
       "  'G04BA03',\n",
       "  'V04CE01',\n",
       "  'V06DC01',\n",
       "  'V04CA02'],\n",
       " 'CID000000214': ['G04BE01', 'C01EA01'],\n",
       " 'CID000000232': ['B05XB01'],\n",
       " 'CID000000247': ['A16AA06', 'A09AB02'],\n",
       " 'CID000000271': ['A07XA03', 'A12AA20'],\n",
       " 'CID000000298': ['S03AA08',\n",
       "  'G01AA05',\n",
       "  'J01BA01',\n",
       "  'S01AA01',\n",
       "  'D10AF03',\n",
       "  'D06AX02',\n",
       "  'S02AA01'],\n",
       " 'CID000000303': ['A05AA03'],\n",
       " 'CID000000311': ['A09AB04'],\n",
       " 'CID000000312': ['B05XA13', 'A09AB03'],\n",
       " 'CID000000338': ['S01BC08', 'N02BA12', 'N02BA04', 'D01AE12'],\n",
       " 'CID000000401': ['J04AB01'],\n",
       " 'CID000000444': ['N06AX12'],\n",
       " 'CID000000450': ['L02AA02', 'G03CA03'],\n",
       " 'CID000000453': ['A06AG07',\n",
       "  'A06AD16',\n",
       "  'A06AD18',\n",
       "  'B05CX02',\n",
       "  'B05CX04',\n",
       "  'B05BC01',\n",
       "  'V04CC01'],\n",
       " 'CID000000564': ['B02AA01'],\n",
       " 'CID000000581': ['R05CB01', 'S01XA08', 'V03AB23'],\n",
       " 'CID000000596': ['L01BC01'],\n",
       " 'CID000000598': ['R05CB05', 'V03AF01'],\n",
       " 'CID000000612': ['G01AD01'],\n",
       " 'CID000000679': ['G04BX13', 'M02AX03'],\n",
       " 'CID000000681': ['C01CA04'],\n",
       " 'CID000000698': ['G03CC04', 'G03CA07'],\n",
       " 'CID000000727': ['P03AB02'],\n",
       " 'CID000000738': ['A16AA03'],\n",
       " 'CID000000750': ['B05CX03'],\n",
       " 'CID000000753': ['A06AG04', 'A06AX01'],\n",
       " 'CID000000772': ['B01AB05'],\n",
       " 'CID000000774': ['V04CG03', 'L03AX14'],\n",
       " 'CID000000785': ['D11AX11'],\n",
       " 'CID000000815': ['A07AA08', 'J01GB04', 'S01AA24'],\n",
       " 'CID000000838': ['R03AA01',\n",
       "  'R01AA14',\n",
       "  'C01CA24',\n",
       "  'S01EA01',\n",
       "  'A01AD01',\n",
       "  'B02BC09'],\n",
       " 'CID000000853': ['H03AA01', 'C10AX01'],\n",
       " 'CID000000861': ['H03AA02'],\n",
       " 'CID000000888': ['A12CC30'],\n",
       " 'CID000000896': ['N05CH01'],\n",
       " 'CID000000937': ['C04AC01', 'C10AD02'],\n",
       " 'CID000000942': ['N07BA01'],\n",
       " 'CID000000948': ['N01AX13'],\n",
       " 'CID000000951': ['C01CA03'],\n",
       " 'CID000001046': ['J04AK01'],\n",
       " 'CID000001054': ['A11HA02'],\n",
       " 'CID000001065': ['P01BC01', 'C01BA01'],\n",
       " 'CID000001071': ['D10AD02', 'A11CA01', 'R01AX02', 'S01XA02'],\n",
       " 'CID000001084': ['V03AB06'],\n",
       " 'CID000001125': ['A16AX07'],\n",
       " 'CID000001130': ['A11DA01'],\n",
       " 'CID000001134': ['J05AF11'],\n",
       " 'CID000001206': ['N06BA03'],\n",
       " 'CID000001301': ['G02CC02', 'M01AE02', 'M02AA12'],\n",
       " 'CID000001546': ['L01BB04'],\n",
       " 'CID000001690': ['L01DB03', 'L01DB01'],\n",
       " 'CID000001775': ['N03AB02'],\n",
       " 'CID000001798': ['S01EC05'],\n",
       " 'CID000001805': ['L01BC07'],\n",
       " 'CID000001971': ['J05AF06'],\n",
       " 'CID000001972': ['J02AA01', 'A07AA07', 'A01AB04', 'G01AA03'],\n",
       " 'CID000001978': ['C07AB04'],\n",
       " 'CID000001983': ['N02BE01'],\n",
       " 'CID000001986': ['S01EC01'],\n",
       " 'CID000001990': ['G04BX03'],\n",
       " 'CID000002019': ['L01DA01'],\n",
       " 'CID000002021': ['J01XX04'],\n",
       " 'CID000002022': ['D06BB03', 'J05AB01', 'S01AD03'],\n",
       " 'CID000002082': ['P02CA03'],\n",
       " 'CID000002083': ['R03AC02', 'R03CC02'],\n",
       " 'CID000002088': ['M05BA04'],\n",
       " 'CID000002092': ['G04CA01'],\n",
       " 'CID000002094': ['M04AA01'],\n",
       " 'CID000002099': ['A03AE01'],\n",
       " 'CID000002118': ['N05BA12'],\n",
       " 'CID000002123': ['L01XX03'],\n",
       " 'CID000002130': ['N04BB01'],\n",
       " 'CID000002133': ['D07AC11'],\n",
       " 'CID000002140': ['V08AA01'],\n",
       " 'CID000002141': ['V03AF05'],\n",
       " 'CID000002142': ['D06AX12', 'J01GB06', 'S01AA21'],\n",
       " 'CID000002145': ['L02BG01'],\n",
       " 'CID000002148': ['V04CH30'],\n",
       " 'CID000002153': ['R03DA04'],\n",
       " 'CID000002156': ['C01BD01'],\n",
       " 'CID000002159': ['N05AL05'],\n",
       " 'CID000002160': ['N06AA09'],\n",
       " 'CID000002161': ['R03DX01', 'A01AD07'],\n",
       " 'CID000002162': ['C08CA01'],\n",
       " 'CID000002163': ['N05CA02'],\n",
       " 'CID000002168': ['D01AE16'],\n",
       " 'CID000002170': ['N06AA17'],\n",
       " 'CID000002171': ['J01CA04'],\n",
       " 'CID000002173': ['S01AA19', 'J01CA01'],\n",
       " 'CID000002177': ['J05AE05'],\n",
       " 'CID000002179': ['L01XX01'],\n",
       " 'CID000002182': ['L01XX35'],\n",
       " 'CID000002187': ['L02BG03'],\n",
       " 'CID000002215': ['N04BC07', 'G04BE07'],\n",
       " 'CID000002216': ['S01EA03'],\n",
       " 'CID000002232': ['B01AE03'],\n",
       " 'CID000002244': ['N02BA01', 'B01AC06', 'A01AD05'],\n",
       " 'CID000002249': ['C07AB03', 'C07AB11'],\n",
       " 'CID000002250': ['C10AA05'],\n",
       " 'CID000002265': ['L04AX01'],\n",
       " 'CID000002266': ['D10AX03'],\n",
       " 'CID000002267': ['S01GX07', 'R01AC03', 'R06AX19'],\n",
       " 'CID000002269': ['J01FA10', 'S01AA26'],\n",
       " 'CID000002274': ['J01DF01'],\n",
       " 'CID000002284': ['M03BX01'],\n",
       " 'CID000002307': ['D07AC15', 'A07EA07', 'R01AD01', 'R03BA01'],\n",
       " 'CID000002311': ['C09AA07'],\n",
       " 'CID000002315': ['C03AA01'],\n",
       " 'CID000002337': ['C05AD03', 'N01BA05', 'R02AD01', 'D04AB04'],\n",
       " 'CID000002344': ['N04AC01'],\n",
       " 'CID000002345': ['P03AX01'],\n",
       " 'CID000002349': ['J01CE01', 'S01AA14'],\n",
       " 'CID000002351': ['C08EA02'],\n",
       " 'CID000002366': ['N07CA01'],\n",
       " 'CID000002369': ['S01ED02', 'C07AB05'],\n",
       " 'CID000002370': ['N07AB02'],\n",
       " 'CID000002375': ['L02BB03'],\n",
       " 'CID000002391': ['A06AB02', 'A06AG02'],\n",
       " 'CID000002405': ['C07AB07'],\n",
       " 'CID000002431': ['C01BD02'],\n",
       " 'CID000002435': ['S01EA05'],\n",
       " 'CID000002441': ['N05BA08'],\n",
       " 'CID000002442': ['R05CB02'],\n",
       " 'CID000002443': ['N04BC01', 'G02CB01'],\n",
       " 'CID000002462': ['R01AD05', 'A07EA06', 'R03BA02', 'D07AC09'],\n",
       " 'CID000002471': ['C03CA02'],\n",
       " 'CID000002474': ['N01BB01', 'N01BB10'],\n",
       " 'CID000002476': ['N07BC01', 'N02AE01'],\n",
       " 'CID000002477': ['N05BE01'],\n",
       " 'CID000002478': ['L01AB01'],\n",
       " 'CID000002484': ['D01AE23'],\n",
       " 'CID000002487': ['N02AF01'],\n",
       " 'CID000002512': ['G02CB03', 'N04BC06'],\n",
       " 'CID000002519': ['N06BC01'],\n",
       " 'CID000002520': ['C08DA01'],\n",
       " 'CID000002522': ['D05AX02'],\n",
       " 'CID000002524': ['D05AX03', 'A11CC04'],\n",
       " 'CID000002541': ['C09CA06'],\n",
       " 'CID000002547': ['J04AB30'],\n",
       " 'CID000002548': ['M02AB02', 'M02AB01', 'N01BX04'],\n",
       " 'CID000002550': ['C09AA01'],\n",
       " 'CID000002551': ['S01EB02', 'N07AB01'],\n",
       " 'CID000002554': ['N03AF01'],\n",
       " 'CID000002559': ['J01CA03'],\n",
       " 'CID000002564': ['R06AA08'],\n",
       " 'CID000002576': ['M03BA02'],\n",
       " 'CID000002578': ['L01AD01'],\n",
       " 'CID000002583': ['S01ED05', 'C07AA15'],\n",
       " 'CID000002585': ['C07AG02'],\n",
       " 'CID000002609': ['J01DC04'],\n",
       " 'CID000002610': ['J01DB05'],\n",
       " 'CID000002617': ['J01DB04'],\n",
       " 'CID000002622': ['J01DE01'],\n",
       " 'CID000002631': ['J01DD01'],\n",
       " 'CID000002637': ['J01DC01'],\n",
       " 'CID000002646': ['J01DC10'],\n",
       " 'CID000002650': ['J01DD02'],\n",
       " 'CID000002654': ['J01DD14'],\n",
       " 'CID000002655': ['J01DD07'],\n",
       " 'CID000002656': ['J01DD04'],\n",
       " 'CID000002658': ['J01DC02'],\n",
       " 'CID000002662': ['M01AH01', 'L01XX33'],\n",
       " 'CID000002663': ['C07AB08'],\n",
       " 'CID000002666': ['J01DB01'],\n",
       " 'CID000002675': ['J01DD08'],\n",
       " 'CID000002676': ['C10AA06'],\n",
       " 'CID000002678': ['R06AE09', 'R06AE07'],\n",
       " 'CID000002684': ['N07AX03'],\n",
       " 'CID000002708': ['L01AA02'],\n",
       " 'CID000002712': ['N05BA02'],\n",
       " 'CID000002713': ['R02AA05',\n",
       "  'S01AX09',\n",
       "  'B05CA02',\n",
       "  'A01AB03',\n",
       "  'D08AC02',\n",
       "  'S03AA04',\n",
       "  'S02AA09',\n",
       "  'D09AA12'],\n",
       " 'CID000002719': ['N06DX02', 'P01BA01'],\n",
       " 'CID000002720': ['C03AA04'],\n",
       " 'CID000002725': ['R06AB04', 'R06AB02'],\n",
       " 'CID000002726': ['N05AA01'],\n",
       " 'CID000002727': ['A10BB02'],\n",
       " 'CID000002732': ['C03BA04'],\n",
       " 'CID000002733': ['M03BB03'],\n",
       " 'CID000002749': ['G01AX12', 'D01AE14'],\n",
       " 'CID000002751': ['C09AA08'],\n",
       " 'CID000002756': ['A02BA01'],\n",
       " 'CID000002762': ['J01MB06'],\n",
       " 'CID000002764': ['J01MA02', 'S01AX13', 'S02AA15', 'S03AA07'],\n",
       " 'CID000002767': ['L01XA01'],\n",
       " 'CID000002771': ['N06AB10', 'N06AB04'],\n",
       " 'CID000002781': ['R06AA04', 'D04AA14'],\n",
       " 'CID000002786': ['G01AA10', 'J01FF01', 'D10AF01'],\n",
       " 'CID000002789': ['N05BA09'],\n",
       " 'CID000002792': ['D07AB01', 'S01BA09'],\n",
       " 'CID000002794': ['J04BA01'],\n",
       " 'CID000002800': ['G03GB02'],\n",
       " 'CID000002801': ['N06AA04'],\n",
       " 'CID000002802': ['N03AE01'],\n",
       " 'CID000002803': ['C02AC01', 'N02CX02', 'S01EA04'],\n",
       " 'CID000002806': ['B01AC04'],\n",
       " 'CID000002812': ['A01AB18', 'D01AC01', 'G01AF02'],\n",
       " 'CID000002818': ['N05AH02'],\n",
       " 'CID000002826': ['S01HA01', 'N01BC01', 'R02AD03', 'S02DA02'],\n",
       " 'CID000002828': ['R05DA04'],\n",
       " 'CID000002833': ['M04AC01'],\n",
       " 'CID000002881': ['A07EB01', 'R01AC01', 'R03BC01', 'S01GX01', 'D11AH03'],\n",
       " 'CID000002891': ['B03BA01'],\n",
       " 'CID000002895': ['M03BX08'],\n",
       " 'CID000002905': ['S01FA04'],\n",
       " 'CID000002907': ['L01AA01'],\n",
       " 'CID000002909': ['S01XA18', 'L04AD01'],\n",
       " 'CID000002913': ['R06AX02'],\n",
       " 'CID000002949': ['G03XA01'],\n",
       " 'CID000002951': ['M03CA01'],\n",
       " 'CID000002955': ['D10AX05', 'J04BA02'],\n",
       " 'CID000002958': ['L01DB02'],\n",
       " 'CID000002972': ['V03AC02'],\n",
       " 'CID000002973': ['V03AC01'],\n",
       " 'CID000002978': ['A04AD10'],\n",
       " 'CID000002995': ['N06AA01'],\n",
       " 'CID000003000': ['D07AC03', 'D07XC02'],\n",
       " 'CID000003003': ['S03BA03',\n",
       "  'D07AC01',\n",
       "  'S02BA06',\n",
       "  'D07XB05',\n",
       "  'C05AA05',\n",
       "  'C05AA09',\n",
       "  'R01AD03',\n",
       "  'S03BA01',\n",
       "  'A01AC02',\n",
       "  'H02AB01',\n",
       "  'S01BA06',\n",
       "  'R03BA04',\n",
       "  'S01CB01',\n",
       "  'S01CB04',\n",
       "  'H02AB02',\n",
       "  'D07XC01',\n",
       "  'R01AD06',\n",
       "  'D07AB19',\n",
       "  'D10AA03',\n",
       "  'S01BA01',\n",
       "  'S02BA07',\n",
       "  'A07EA04'],\n",
       " 'CID000003007': ['N06BA01', 'N06BA02'],\n",
       " 'CID000003008': ['R05DA09'],\n",
       " 'CID000003009': ['D11AX16', 'P01CX03'],\n",
       " 'CID000003015': ['G03HA01'],\n",
       " 'CID000003016': ['N05BA01'],\n",
       " 'CID000003019': ['V03AH01', 'C02DA01'],\n",
       " 'CID000003032': ['D11AX18', 'M02AA15', 'M01AB05', 'S01BC03'],\n",
       " 'CID000003038': ['S01EC02'],\n",
       " 'CID000003040': ['J01CF01'],\n",
       " 'CID000003042': ['A03AA07'],\n",
       " 'CID000003043': ['J05AF02'],\n",
       " 'CID000003059': ['N02BA11'],\n",
       " 'CID000003060': ['D07AC19'],\n",
       " 'CID000003062': ['C01AA05'],\n",
       " 'CID000003063': ['N02AA08'],\n",
       " 'CID000003066': ['N02CA01'],\n",
       " 'CID000003075': ['C08DB01'],\n",
       " 'CID000003080': ['V03AB09'],\n",
       " 'CID000003100': ['D04AA32', 'N04AB01', 'R06AA02'],\n",
       " 'CID000003105': ['S01EA02'],\n",
       " 'CID000003108': ['B01AC07'],\n",
       " 'CID000003114': ['C01BA03'],\n",
       " 'CID000003117': ['P03AA04', 'N07BB01'],\n",
       " 'CID000003121': ['N03AG01'],\n",
       " 'CID000003125': ['C02KB01'],\n",
       " 'CID000003143': ['L01CD02'],\n",
       " 'CID000003148': ['A04AA04'],\n",
       " 'CID000003151': ['A03FA03'],\n",
       " 'CID000003152': ['N06DA02'],\n",
       " 'CID000003154': ['S01EC03'],\n",
       " 'CID000003155': ['N06AA16'],\n",
       " 'CID000003156': ['R07AB01'],\n",
       " 'CID000003157': ['C02CA04'],\n",
       " 'CID000003158': ['N06AA12'],\n",
       " 'CID000003168': ['N05AD08', 'N01AX01'],\n",
       " 'CID000003182': ['R03DA01'],\n",
       " 'CID000003198': ['D01AC03', 'G01AF05'],\n",
       " 'CID000003199': ['L01CX01'],\n",
       " 'CID000003203': ['J05AG03'],\n",
       " 'CID000003219': ['S01GX06'],\n",
       " 'CID000003222': ['C09AA02'],\n",
       " 'CID000003226': ['N01AB04'],\n",
       " 'CID000003241': ['S01GX10', 'R06AX24'],\n",
       " 'CID000003249': ['A11CC01'],\n",
       " 'CID000003250': ['G02AB03'],\n",
       " 'CID000003251': ['N02CA02'],\n",
       " 'CID000003255': ['S01AA17', 'J01FA01', 'D10AF02'],\n",
       " 'CID000003261': ['N05CD04'],\n",
       " 'CID000003269': ['G03CC06', 'G03CA04'],\n",
       " 'CID000003278': ['C03CC01'],\n",
       " 'CID000003279': ['J04AK02'],\n",
       " 'CID000003291': ['N03AD01'],\n",
       " 'CID000003292': ['N03AB01'],\n",
       " 'CID000003305': ['M05BA01'],\n",
       " 'CID000003308': ['M01AB08'],\n",
       " 'CID000003310': ['L01CB01'],\n",
       " 'CID000003324': ['J05AB09', 'S01AD07'],\n",
       " 'CID000003325': ['A02BA03'],\n",
       " 'CID000003331': ['N03AX10'],\n",
       " 'CID000003333': ['C08CA02'],\n",
       " 'CID000003339': ['C10AB05'],\n",
       " 'CID000003340': ['C01CA19'],\n",
       " 'CID000003342': ['M01AE04'],\n",
       " 'CID000003345': ['N01AH01', 'N02AB03'],\n",
       " 'CID000003348': ['R06AX26'],\n",
       " 'CID000003350': ['G04CB01', 'D11AX10'],\n",
       " 'CID000003354': ['G04BD02'],\n",
       " 'CID000003355': ['C01BC04'],\n",
       " 'CID000003364': ['J01CF05'],\n",
       " 'CID000003365': ['J02AC01', 'D01AC15'],\n",
       " 'CID000003366': ['J02AX01', 'D01AE21'],\n",
       " 'CID000003367': ['L01BB05'],\n",
       " 'CID000003372': ['N05AB02'],\n",
       " 'CID000003373': ['V03AB25'],\n",
       " 'CID000003375': ['D07XB01', 'D07AB03', 'D07AC10'],\n",
       " 'CID000003379': ['R01AD04', 'R03BA03'],\n",
       " 'CID000003380': ['N05CD03'],\n",
       " 'CID000003381': ['S01BA15', 'D07AC04', 'C05AA10', 'S02BA08'],\n",
       " 'CID000003382': ['D07AC08', 'C05AA11'],\n",
       " 'CID000003384': ['S01CB05',\n",
       "  'D07AB06',\n",
       "  'D07XB04',\n",
       "  'C05AA06',\n",
       "  'S01BA07',\n",
       "  'D10AA01'],\n",
       " 'CID000003385': ['L01BC02'],\n",
       " 'CID000003386': ['N06AB03'],\n",
       " 'CID000003387': ['G03BA01'],\n",
       " 'CID000003392': ['D07AC07'],\n",
       " 'CID000003393': ['N05CD01'],\n",
       " 'CID000003394': ['S01BC04', 'R02AX01', 'M02AA19', 'M01AE09'],\n",
       " 'CID000003397': ['L02BB01'],\n",
       " 'CID000003403': ['C10AA04'],\n",
       " 'CID000003404': ['N06AB08'],\n",
       " 'CID000003405': ['B03BB01'],\n",
       " 'CID000003406': ['V03AB34'],\n",
       " 'CID000003410': ['R03AC13'],\n",
       " 'CID000003414': ['J05AD01'],\n",
       " 'CID000003417': ['J01XX01'],\n",
       " 'CID000003419': ['C09AA09'],\n",
       " 'CID000003425': ['L01XX39'],\n",
       " 'CID000003440': ['C03CA01'],\n",
       " 'CID000003443': ['D09AA02', 'D06AX01', 'J01XC01', 'S01AA13'],\n",
       " 'CID000003446': ['N03AX12'],\n",
       " 'CID000003449': ['N06DA04'],\n",
       " 'CID000003454': ['J05AB06', 'S01AD09'],\n",
       " 'CID000003461': ['L01BC05'],\n",
       " 'CID000003462': ['G02AD03'],\n",
       " 'CID000003463': ['C10AB04'],\n",
       " 'CID000003467': ['J01GB03', 'S02AA14', 'D06AX07', 'S03AA06', 'S01AA11'],\n",
       " 'CID000003475': ['A10BB09'],\n",
       " 'CID000003476': ['A10BB12'],\n",
       " 'CID000003478': ['A10BB07'],\n",
       " 'CID000003488': ['A10BB01'],\n",
       " 'CID000003494': ['A03AB02'],\n",
       " 'CID000003510': ['A04AA02'],\n",
       " 'CID000003512': ['D01AA08', 'D01BA01'],\n",
       " 'CID000003516': ['R05CA03'],\n",
       " 'CID000003519': ['C02AC02'],\n",
       " 'CID000003553': ['D07AD02'],\n",
       " 'CID000003559': ['N05AD01'],\n",
       " 'CID000003598': ['D08AE01'],\n",
       " 'CID000003623': ['S01FA05'],\n",
       " 'CID000003636': ['A03BB01'],\n",
       " 'CID000003637': ['C02DB02'],\n",
       " 'CID000003639': ['C03AA03'],\n",
       " 'CID000003640': ['C05AA01',\n",
       "  'A07EA02',\n",
       "  'D07XA01',\n",
       "  'H02AB09',\n",
       "  'A01AC03',\n",
       "  'S01BA02',\n",
       "  'S01CB03',\n",
       "  'D07AA02',\n",
       "  'S02BA01'],\n",
       " 'CID000003642': ['D07AB02'],\n",
       " 'CID000003647': ['C03AA02'],\n",
       " 'CID000003648': ['N02AA03'],\n",
       " 'CID000003652': ['P01BA02'],\n",
       " 'CID000003657': ['L01XX05'],\n",
       " 'CID000003658': ['N05BB01'],\n",
       " 'CID000003661': ['V03AB14', 'A03BA01', 'A03BA03', 'S01FA01'],\n",
       " 'CID000003672': ['C01EB16', 'M01AE14', 'M01AE01', 'M02AA13', 'G02CC01'],\n",
       " 'CID000003675': ['N06AF03'],\n",
       " 'CID000003676': ['N01BB02',\n",
       "  'R02AD02',\n",
       "  'S02DA01',\n",
       "  'D04AB01',\n",
       "  'S01HA07',\n",
       "  'C05AD01',\n",
       "  'C01BB01'],\n",
       " 'CID000003685': ['L01DB06'],\n",
       " 'CID000003687': ['S01AD01', 'D06BB01', 'J05AB02'],\n",
       " 'CID000003690': ['L01AA06'],\n",
       " 'CID000003696': ['N06AA02'],\n",
       " 'CID000003702': ['C03BA11'],\n",
       " 'CID000003706': ['J05AE02'],\n",
       " 'CID000003715': ['M01AB01', 'C01EB03', 'S01BC01', 'M02AA23'],\n",
       " 'CID000003724': ['V08AB09'],\n",
       " 'CID000003730': ['V08AB02'],\n",
       " 'CID000003734': ['V08AB04'],\n",
       " 'CID000003736': ['V08AB05'],\n",
       " 'CID000003737': ['V08AA04'],\n",
       " 'CID000003738': ['V08AB06'],\n",
       " 'CID000003739': ['V08AC04'],\n",
       " 'CID000003741': ['V08AB07'],\n",
       " 'CID000003742': ['V08AB03'],\n",
       " 'CID000003743': ['V08AB12'],\n",
       " 'CID000003746': ['R03BB01', 'R01AX03'],\n",
       " 'CID000003749': ['C09CA04'],\n",
       " 'CID000003750': ['L01XX19'],\n",
       " 'CID000003759': ['N06AF01'],\n",
       " 'CID000003763': ['N01AB06'],\n",
       " 'CID000003767': ['J04AC01'],\n",
       " 'CID000003779': ['R03AB02', 'C01CA02', 'R03CB01'],\n",
       " 'CID000003780': ['C01DA08', 'C05AE02'],\n",
       " 'CID000003783': ['C04AA01'],\n",
       " 'CID000003784': ['C08CA03'],\n",
       " 'CID000003793': ['J02AC02'],\n",
       " 'CID000003821': ['N01AX03', 'N01AX14'],\n",
       " 'CID000003823': ['D01AC08', 'J02AB02', 'G01AF11'],\n",
       " 'CID000003825': ['M01AE17', 'M01AE03', 'M02AA10'],\n",
       " 'CID000003826': ['S01BC05', 'M01AB15'],\n",
       " 'CID000003827': ['R06AX17', 'S01GX08'],\n",
       " 'CID000003830': ['B06AA03'],\n",
       " 'CID000003869': ['C07AG01'],\n",
       " 'CID000003872': ['A06AD11'],\n",
       " 'CID000003877': ['J05AF05'],\n",
       " 'CID000003878': ['N03AX09'],\n",
       " 'CID000003883': ['A02BC03'],\n",
       " 'CID000003890': ['S01EE01'],\n",
       " 'CID000003899': ['L04AA13'],\n",
       " 'CID000003902': ['L02BG04'],\n",
       " 'CID000003911': ['L02AE02'],\n",
       " 'CID000003914': ['S01ED03'],\n",
       " 'CID000003915': ['S01GX02', 'R01AC02'],\n",
       " 'CID000003916': ['N05AA02'],\n",
       " 'CID000003928': ['J01FF02'],\n",
       " 'CID000003929': ['J01XX08'],\n",
       " 'CID000003937': ['C09AA03'],\n",
       " 'CID000003938': ['G02CB02', 'N02CA07'],\n",
       " 'CID000003948': ['J01MA07', 'S01AX17'],\n",
       " 'CID000003950': ['L01AD02'],\n",
       " 'CID000003954': ['A07DA03'],\n",
       " 'CID000003957': ['R06AX13'],\n",
       " 'CID000003958': ['N05BA06'],\n",
       " 'CID000003961': ['C09CA01'],\n",
       " 'CID000003962': ['C10AA02'],\n",
       " 'CID000003964': ['N05AH01'],\n",
       " 'CID000003998': ['D06BA03'],\n",
       " 'CID000004004': ['P03AX03'],\n",
       " 'CID000004011': ['N06AA21'],\n",
       " 'CID000004030': ['P02CA01'],\n",
       " 'CID000004031': ['A03AA04'],\n",
       " 'CID000004032': ['C02BB01'],\n",
       " 'CID000004033': ['L01AA05'],\n",
       " 'CID000004034': ['R06AE05'],\n",
       " 'CID000004036': ['M01AG04', 'M02AA18'],\n",
       " 'CID000004043': ['S01BA08'],\n",
       " 'CID000004044': ['M01AG01'],\n",
       " 'CID000004046': ['P01BC02'],\n",
       " 'CID000004051': ['M01AC06'],\n",
       " 'CID000004053': ['L01AA03'],\n",
       " 'CID000004054': ['N06DX01'],\n",
       " 'CID000004057': ['A03AB12'],\n",
       " 'CID000004058': ['N02AB02'],\n",
       " 'CID000004060': ['N03AB04'],\n",
       " 'CID000004062': ['N01BB03'],\n",
       " 'CID000004064': ['N05BC01'],\n",
       " 'CID000004075': ['A07EC02'],\n",
       " 'CID000004086': ['R03AB03', 'R03CB03'],\n",
       " 'CID000004091': ['A10BA02'],\n",
       " 'CID000004095': ['N07BC02'],\n",
       " 'CID000004101': ['J01XX05'],\n",
       " 'CID000004107': ['M03BA03'],\n",
       " 'CID000004112': ['L04AX03', 'L01BA01'],\n",
       " 'CID000004114': ['D05BA02', 'D05AD02'],\n",
       " 'CID000004120': ['S01FA03', 'A03BB03'],\n",
       " 'CID000004121': ['C03AA08'],\n",
       " 'CID000004138': ['C02AB01', 'C02AB02'],\n",
       " 'CID000004139': ['V04CG05', 'V03AB17'],\n",
       " 'CID000004140': ['G02AB01'],\n",
       " 'CID000004158': ['N06BA11', 'N06BA04'],\n",
       " 'CID000004159': ['H02AB04', 'D10AA02', 'D07AA01'],\n",
       " 'CID000004160': ['G03BA02', 'G03EK01'],\n",
       " 'CID000004163': ['N02CA04'],\n",
       " 'CID000004168': ['A03FA01'],\n",
       " 'CID000004170': ['C03BA08'],\n",
       " 'CID000004171': ['C07AB02'],\n",
       " 'CID000004173': ['D06BX01', 'A01AB17', 'G01AF01', 'P01AB01', 'J01XD01'],\n",
       " 'CID000004174': ['V04CD01'],\n",
       " 'CID000004178': ['C01BB02'],\n",
       " 'CID000004184': ['N06AX03'],\n",
       " 'CID000004189': ['A07AC01',\n",
       "  'S02AA13',\n",
       "  'G01AF04',\n",
       "  'D01AC02',\n",
       "  'A01AB09',\n",
       "  'J02AB01'],\n",
       " 'CID000004192': ['N05CD08'],\n",
       " 'CID000004195': ['C01CA17'],\n",
       " 'CID000004196': ['G03XB01'],\n",
       " 'CID000004197': ['C01CE02'],\n",
       " 'CID000004201': ['D11AX01', 'C02DC01'],\n",
       " 'CID000004205': ['N06AX11'],\n",
       " 'CID000004211': ['L01XX23'],\n",
       " 'CID000004212': ['L01DB07'],\n",
       " 'CID000004235': ['N06AG02'],\n",
       " 'CID000004236': ['N06BA07'],\n",
       " 'CID000004248': ['R03DC03'],\n",
       " 'CID000004253': ['N02AA01'],\n",
       " 'CID000004259': ['J01MA14', 'S01AX22'],\n",
       " 'CID000004264': ['R01AX06', 'D06AX09'],\n",
       " 'CID000004272': ['L04AA06'],\n",
       " 'CID000004409': ['M01AX01'],\n",
       " 'CID000004411': ['C07AA12'],\n",
       " 'CID000004419': ['N02AF02'],\n",
       " 'CID000004421': ['J01MB02'],\n",
       " 'CID000004425': ['V03AB15'],\n",
       " 'CID000004428': ['N07BB04'],\n",
       " 'CID000004436': ['R01AB02', 'S01GA01', 'R01AA08'],\n",
       " 'CID000004440': ['N02CC02'],\n",
       " 'CID000004443': ['A10BX03'],\n",
       " 'CID000004449': ['N06AX06'],\n",
       " 'CID000004450': ['N02BG06'],\n",
       " 'CID000004451': ['J05AE04'],\n",
       " 'CID000004454': ['S03AA01',\n",
       "  'A01AB08',\n",
       "  'B05CA09',\n",
       "  'S02AA07',\n",
       "  'A07AA01',\n",
       "  'S01AA07',\n",
       "  'J01GB05',\n",
       "  'R02AB01',\n",
       "  'R01AX08',\n",
       "  'D09AA01',\n",
       "  'S01AA03',\n",
       "  'D06AX04'],\n",
       " 'CID000004456': ['N07AA01', 'S01EB06'],\n",
       " 'CID000004463': ['J05AG01'],\n",
       " 'CID000004473': ['C08CA04'],\n",
       " 'CID000004485': ['C08CA05'],\n",
       " 'CID000004493': ['L02BB02'],\n",
       " 'CID000004497': ['C08CA06'],\n",
       " 'CID000004499': ['C08CA07'],\n",
       " 'CID000004506': ['N05CD02'],\n",
       " 'CID000004509': ['J01XE01'],\n",
       " 'CID000004510': ['C05AE01', 'C01DA02'],\n",
       " 'CID000004513': ['A02BA04'],\n",
       " 'CID000004536': ['G03DC02', 'G03AC01'],\n",
       " 'CID000004539': ['J01MA06', 'S01AX12'],\n",
       " 'CID000004542': ['G03AC03'],\n",
       " 'CID000004543': ['N06AA10'],\n",
       " 'CID000004547': ['A10BX02'],\n",
       " 'CID000004568': ['D01AA01', 'G01AA01', 'A07AA02'],\n",
       " 'CID000004583': ['J01MA01', 'J01MA12', 'S01AX19', 'S02AA16', 'S01AX11'],\n",
       " 'CID000004585': ['N05AH03'],\n",
       " 'CID000004594': ['A02BC05', 'A02BC01'],\n",
       " 'CID000004595': ['A04AA01'],\n",
       " 'CID000004599': ['A08AB01'],\n",
       " 'CID000004601': ['N04AB02', 'M03BC01'],\n",
       " 'CID000004603': ['J05AH02'],\n",
       " 'CID000004607': ['J01CF04'],\n",
       " 'CID000004609': ['L01XA03'],\n",
       " 'CID000004614': ['M01AE12'],\n",
       " 'CID000004616': ['N05BA04'],\n",
       " 'CID000004623': ['G01AF17', 'D01AC11'],\n",
       " 'CID000004631': ['C07AA02'],\n",
       " 'CID000004634': ['G04BD04'],\n",
       " 'CID000004635': ['N02AA05'],\n",
       " 'CID000004638': ['A14AA05'],\n",
       " 'CID000004649': ['J04AA01', 'J04AA02'],\n",
       " 'CID000004666': ['L01CD01'],\n",
       " 'CID000004673': ['M05BA03'],\n",
       " 'CID000004675': ['M03AC01'],\n",
       " 'CID000004678': ['A11HA30', 'S01XA12', 'D03AX03'],\n",
       " 'CID000004679': ['A02BC02'],\n",
       " 'CID000004680': ['A03AD01', 'G04BE02'],\n",
       " 'CID000004689': ['A07AA06'],\n",
       " 'CID000004691': ['N06AB05'],\n",
       " 'CID000004724': ['C07AA23'],\n",
       " 'CID000004725': ['J05AB13', 'D06BB06'],\n",
       " 'CID000004727': ['M01CC01'],\n",
       " 'CID000004730': ['J01CE02'],\n",
       " 'CID000004736': ['N02AD01'],\n",
       " 'CID000004737': ['N05CA01'],\n",
       " 'CID000004739': ['L01XX08'],\n",
       " 'CID000004740': ['C04AD03'],\n",
       " 'CID000004745': ['N04BC02'],\n",
       " 'CID000004746': ['C08EX02'],\n",
       " 'CID000004747': ['N05AC01'],\n",
       " 'CID000004748': ['N05AB03'],\n",
       " 'CID000004756': ['G04BX06'],\n",
       " 'CID000004763': ['N03AA02'],\n",
       " 'CID000004768': ['C04AX02'],\n",
       " 'CID000004771': ['A08AA01'],\n",
       " 'CID000004775': ['A16AX03'],\n",
       " 'CID000004782': ['C01CA06',\n",
       "  'S01GA05',\n",
       "  'R01AB01',\n",
       "  'R01AA04',\n",
       "  'R01BA03',\n",
       "  'S01FB01'],\n",
       " 'CID000004786': ['R01BA01', 'A08AA07'],\n",
       " 'CID000004810': ['C02AC05'],\n",
       " 'CID000004811': ['V03AB19', 'S01EB05'],\n",
       " 'CID000004812': ['B02BA01'],\n",
       " 'CID000004819': ['N07AX01', 'S01EB01'],\n",
       " 'CID000004828': ['C07AA03'],\n",
       " 'CID000004829': ['A10BG03'],\n",
       " 'CID000004834': ['J01CA12'],\n",
       " 'CID000004845': ['R03CC07', 'R03AC08'],\n",
       " 'CID000004865': ['D06BB04'],\n",
       " 'CID000004868': ['S02AA11', 'S03AA03', 'S01AA18', 'J01XB02', 'A07AA05'],\n",
       " 'CID000004870': ['C03AA05'],\n",
       " 'CID000004885': ['N04BC05'],\n",
       " 'CID000004889': ['C10AA03'],\n",
       " 'CID000004891': ['P02BA01'],\n",
       " 'CID000004893': ['C02CA01'],\n",
       " 'CID000004894': ['S02BA03',\n",
       "  'S01BA04',\n",
       "  'S03BA02',\n",
       "  'H02AB06',\n",
       "  'A07EA01',\n",
       "  'S01CB02',\n",
       "  'D07XA02',\n",
       "  'D07AA03',\n",
       "  'C05AA04',\n",
       "  'R01AD02'],\n",
       " 'CID000004900': ['A07EA03', 'H02AB07'],\n",
       " 'CID000004906': ['N01BB04'],\n",
       " 'CID000004908': ['P01BA03'],\n",
       " 'CID000004909': ['N03AA03'],\n",
       " 'CID000004911': ['M04AB01'],\n",
       " 'CID000004913': ['C01BA02'],\n",
       " 'CID000004914': ['N01BA02', 'S01HA05', 'C05AD05'],\n",
       " 'CID000004915': ['L01XB01'],\n",
       " 'CID000004917': ['N05AB04'],\n",
       " 'CID000004920': ['G03DA04'],\n",
       " 'CID000004923': ['P01BB01'],\n",
       " 'CID000004927': ['D04AA10', 'R06AD02'],\n",
       " 'CID000004932': ['C01BC03'],\n",
       " 'CID000004934': ['A03AB05'],\n",
       " 'CID000004935': ['S01HA04'],\n",
       " 'CID000004943': ['N01AX10'],\n",
       " 'CID000004946': ['C07AA05'],\n",
       " 'CID000004976': ['N06AA11'],\n",
       " 'CID000004989': ['P02CC01'],\n",
       " 'CID000004991': ['N07AA02'],\n",
       " 'CID000004992': ['R06AC01', 'D04AA02'],\n",
       " 'CID000004993': ['P01BD01'],\n",
       " 'CID000004999': ['N05CD10'],\n",
       " 'CID000005002': ['N05AH04'],\n",
       " 'CID000005005': ['C09AA06'],\n",
       " 'CID000005029': ['A02BC04', 'M02AA03', 'M01AA05'],\n",
       " 'CID000005032': ['R01BA02', 'S01FB02', 'R01AB05', 'R01AA03', 'R03CA02'],\n",
       " 'CID000005035': ['G03XC01'],\n",
       " 'CID000005038': ['C09AA05'],\n",
       " 'CID000005039': ['A02BA02'],\n",
       " 'CID000005040': ['L04AA10'],\n",
       " 'CID000005051': ['C02AA01'],\n",
       " 'CID000005052': ['C02AA02'],\n",
       " 'CID000005064': ['J05AB04'],\n",
       " 'CID000005070': ['N07XX02'],\n",
       " 'CID000005071': ['J05AC02'],\n",
       " 'CID000005073': ['N05AX08'],\n",
       " 'CID000005076': ['J05AE03'],\n",
       " 'CID000005077': ['N06DA03'],\n",
       " 'CID000005078': ['N02CC04'],\n",
       " 'CID000005090': ['M01AH02'],\n",
       " 'CID000005095': ['N04BC04'],\n",
       " 'CID000005106': ['J01FA06'],\n",
       " 'CID000005152': ['R03AC12'],\n",
       " 'CID000005155': ['J05AF04'],\n",
       " 'CID000005161': ['N02BA06'],\n",
       " 'CID000005184': ['A04AD01', 'S01FA02', 'N05CM05'],\n",
       " 'CID000005193': ['N05CA06'],\n",
       " 'CID000005195': ['N04BD01'],\n",
       " 'CID000005203': ['N06AB06'],\n",
       " 'CID000005206': ['N01AB08'],\n",
       " 'CID000005210': ['A08AA10'],\n",
       " 'CID000005212': ['G04BE03'],\n",
       " 'CID000005214': ['D06BA01'],\n",
       " 'CID000005215': ['J01EC02'],\n",
       " 'CID000005238': ['V09FX04'],\n",
       " 'CID000005245': ['M05BA07'],\n",
       " 'CID000005253': ['C07AA07'],\n",
       " 'CID000005257': ['J01MA09'],\n",
       " 'CID000005267': ['C03DA01'],\n",
       " 'CID000005291': ['L01XE01'],\n",
       " 'CID000005297': ['J01GA01', 'A07AA04'],\n",
       " 'CID000005300': ['L01AD04'],\n",
       " 'CID000005311': ['L01XX38'],\n",
       " 'CID000005314': ['M03AB01'],\n",
       " 'CID000005318': ['D01AC09'],\n",
       " 'CID000005320': ['S01AB04'],\n",
       " 'CID000005329': ['J01EC01'],\n",
       " 'CID000005333': ['J01EB06', 'D06BA05'],\n",
       " 'CID000005352': ['M01AB02'],\n",
       " 'CID000005358': ['N02CC01'],\n",
       " 'CID000005359': ['M01AE07'],\n",
       " 'CID000005372': ['D11AH01', 'L04AD02'],\n",
       " 'CID000005376': ['L02BA01'],\n",
       " 'CID000005379': ['S01AX21', 'J01MA16'],\n",
       " 'CID000005381': ['D05AX05'],\n",
       " 'CID000005391': ['N05CD07'],\n",
       " 'CID000005394': ['L01AX03'],\n",
       " 'CID000005396': ['L01CB02'],\n",
       " 'CID000005401': ['G04CA03'],\n",
       " 'CID000005402': ['D01AE15', 'D01BA02'],\n",
       " 'CID000005403': ['R03AC03', 'R03CC03'],\n",
       " 'CID000005404': ['G01AG02'],\n",
       " 'CID000005408': ['G03BA03'],\n",
       " 'CID000005411': ['C05AD02', 'D04AB06', 'N01BA03', 'S01HA03'],\n",
       " 'CID000005419': ['R01AA06', 'R01AB03', 'S01GA02'],\n",
       " 'CID000005426': ['L04AX02'],\n",
       " 'CID000005430': ['P02CA02', 'D01AC06'],\n",
       " 'CID000005452': ['N05AC02'],\n",
       " 'CID000005453': ['L01AC01'],\n",
       " 'CID000005454': ['N05AF04'],\n",
       " 'CID000005466': ['N03AG06'],\n",
       " 'CID000005468': ['M01AE11'],\n",
       " 'CID000005470': ['G03CX01'],\n",
       " 'CID000005472': ['B01AC05'],\n",
       " 'CID000005478': ['C07AA06', 'S01ED01'],\n",
       " 'CID000005479': ['J01XD02', 'P01AB02'],\n",
       " 'CID000005483': ['R05CB12'],\n",
       " 'CID000005486': ['B01AC17'],\n",
       " 'CID000005487': ['M03BX02'],\n",
       " 'CID000005496': ['J01GB01', 'S01AA12'],\n",
       " 'CID000005503': ['A10BB05'],\n",
       " 'CID000005505': ['A10BB03', 'V04CA01'],\n",
       " 'CID000005508': ['M02AA21', 'M01AB03'],\n",
       " 'CID000005512': ['G04BD07'],\n",
       " 'CID000005514': ['N03AX11'],\n",
       " 'CID000005515': ['L01XX17'],\n",
       " 'CID000005516': ['L02BA02'],\n",
       " 'CID000005523': ['N02AX02'],\n",
       " 'CID000005524': ['R01AA09'],\n",
       " 'CID000005525': ['C09AA10'],\n",
       " 'CID000005526': ['B02AA02'],\n",
       " 'CID000005530': ['N06AF04'],\n",
       " 'CID000005533': ['N06AX05', 'A06AC07'],\n",
       " 'CID000005538': ['L01XX14',\n",
       "  'D10BA01',\n",
       "  'L01XX22',\n",
       "  'D10AD04',\n",
       "  'D10AD01',\n",
       "  'D11AX19'],\n",
       " 'CID000005544': ['D07XB02',\n",
       "  'S01BA05',\n",
       "  'R03BA06',\n",
       "  'R01AD11',\n",
       "  'A01AC01',\n",
       "  'H02AB08',\n",
       "  'D07AB09'],\n",
       " 'CID000005546': ['C03DB02'],\n",
       " 'CID000005556': ['N05CD05'],\n",
       " 'CID000005564': ['D08AE04', 'D09AA06'],\n",
       " 'CID000005566': ['N05AB06'],\n",
       " 'CID000005572': ['N04AA01'],\n",
       " 'CID000005576': ['N03AC02'],\n",
       " 'CID000005578': ['J01EA01'],\n",
       " 'CID000005582': ['P01AX07'],\n",
       " 'CID000005584': ['N06AA06'],\n",
       " 'CID000005591': ['A10BG01'],\n",
       " 'CID000005593': ['S01FA06'],\n",
       " 'CID000005595': ['A04AA03'],\n",
       " 'CID000005596': ['G04BD09'],\n",
       " 'CID000005625': ['J05AG02'],\n",
       " 'CID000005636': ['S01EE02'],\n",
       " 'CID000005645': ['A05AA02', 'A05AA01'],\n",
       " 'CID000005647': ['J05AB11'],\n",
       " 'CID000005650': ['C09CA03'],\n",
       " 'CID000005651': ['A07AA09', 'J01XA01'],\n",
       " 'CID000005656': ['N06AX16'],\n",
       " 'CID000005665': ['N03AG04'],\n",
       " 'CID000005672': ['L01CA04'],\n",
       " 'CID000005717': ['R03DC01'],\n",
       " 'CID000005718': ['J05AF03'],\n",
       " 'CID000005719': ['N05CF03'],\n",
       " 'CID000005721': ['J05AH01'],\n",
       " 'CID000005726': ['S01AD05', 'J05AF01'],\n",
       " 'CID000005727': ['B05XA12'],\n",
       " 'CID000005731': ['N02CC03'],\n",
       " 'CID000005732': ['N05CF02'],\n",
       " 'CID000005734': ['N03AX15'],\n",
       " 'CID000005735': ['N05CF01', 'N05CF04'],\n",
       " 'CID000005746': ['L01DC03'],\n",
       " 'CID000005771': ['H01BB02'],\n",
       " 'CID000005775': ['G04BE05', 'C04AB01'],\n",
       " 'CID000005878': ['A14AA08'],\n",
       " 'CID000005978': ['L01CA02'],\n",
       " 'CID000006018': ['N07XX06'],\n",
       " 'CID000006049': ['V03AB03'],\n",
       " 'CID000006058': ['A16AA04'],\n",
       " 'CID000006238': ['G03DA03'],\n",
       " 'CID000006256': ['S01AD02'],\n",
       " 'CID000006476': ['N03AD03'],\n",
       " 'CID000006503': ['B05BB03', 'B05XX02'],\n",
       " 'CID000006726': ['R06AE03'],\n",
       " 'CID000007029': ['A08AA03'],\n",
       " 'CID000007187': ['D10AE01'],\n",
       " 'CID000007638': ['D11AX13'],\n",
       " 'CID000007699': ['R05DB01'],\n",
       " 'CID000008230': ['H01BA06'],\n",
       " 'CID000008612': ['N01BA04'],\n",
       " 'CID000009034': ['N01AF01', 'N05CA15'],\n",
       " 'CID000009433': ['R03DA05'],\n",
       " 'CID000009904': ['A14AB01', 'S01XA11'],\n",
       " 'CID000010100': ['N02AC04'],\n",
       " 'CID000010340': ['B05XA02', 'B05CB04'],\n",
       " 'CID000010413': ['N07XX04', 'N01AX11'],\n",
       " 'CID000010547': ['S01EB03'],\n",
       " 'CID000010631': ['L02AB02', 'G03AC06', 'G03DA02'],\n",
       " 'CID000012453': ['N05AF05', 'N05AF02'],\n",
       " 'CID000012536': ['S01BA11', 'D07AB08'],\n",
       " 'CID000012555': ['M02AA05', 'M01AX07', 'G02CC03', 'A01AD02'],\n",
       " 'CID000012620': ['D06BB11'],\n",
       " 'CID000013314': ['N05CD06'],\n",
       " 'CID000013342': ['L01CA01'],\n",
       " 'CID000014888': ['L01XX27'],\n",
       " 'CID000015232': ['J01CE08'],\n",
       " 'CID000016230': ['C03DB01'],\n",
       " 'CID000016362': ['N05AG02'],\n",
       " 'CID000016850': ['S01JA01'],\n",
       " 'CID000016886': ['L01BC08'],\n",
       " 'CID000017358': ['V08DA05'],\n",
       " 'CID000018140': ['L01XX11'],\n",
       " 'CID000019090': ['G03DB02', 'G03AC05', 'L02AB01'],\n",
       " 'CID000020585': ['R03DA02'],\n",
       " 'CID000020969': ['D07AB21'],\n",
       " 'CID000022258': ['R05DA03'],\n",
       " 'CID000022318': ['M01CB01'],\n",
       " 'CID000022502': ['J01CE09'],\n",
       " 'CID000023897': ['N05AE02'],\n",
       " 'CID000023954': ['D08AL30', 'S01AX02'],\n",
       " 'CID000024087': ['D01AE13'],\n",
       " 'CID000024424': ['A12CB01'],\n",
       " 'CID000025419': ['M05BA02'],\n",
       " 'CID000025959': ['V03AB31'],\n",
       " 'CID000027400': ['N02CX01'],\n",
       " 'CID000027661': ['C01DA14'],\n",
       " 'CID000027991': ['H01BA02'],\n",
       " 'CID000027993': ['G03CA57'],\n",
       " 'CID000028486': ['N05AN01'],\n",
       " 'CID000030623': ['V03AF02'],\n",
       " 'CID000031072': ['H03BB01'],\n",
       " 'CID000031264': ['N05CC05'],\n",
       " 'CID000031378': ['H02AA02'],\n",
       " 'CID000031477': ['S01ED04'],\n",
       " 'CID000032169': ['N01BB08'],\n",
       " 'CID000032281': ['V04CJ01', 'V04CJ02', 'H01AB01'],\n",
       " 'CID000032797': ['D07AD01'],\n",
       " 'CID000032800': ['V04CC03'],\n",
       " 'CID000034312': ['N03AF02'],\n",
       " 'CID000036339': ['N01AX07'],\n",
       " 'CID000036523': ['V04CM01', 'H01CA01'],\n",
       " 'CID000036811': ['C01CA07'],\n",
       " 'CID000037392': ['P01BX01'],\n",
       " 'CID000037720': ['C05BA04'],\n",
       " 'CID000038904': ['L01XA02'],\n",
       " 'CID000039042': ['C10AB02'],\n",
       " 'CID000039507': ['H02AB12', 'S01BA13'],\n",
       " 'CID000039524': ['N03AX17'],\n",
       " 'CID000039764': ['M03AC03'],\n",
       " 'CID000039860': ['A04AD11'],\n",
       " 'CID000040159': ['P03AC04'],\n",
       " 'CID000040703': ['A03AX04'],\n",
       " 'CID000040973': ['G03AC09'],\n",
       " 'CID000040976': ['G03AC08'],\n",
       " 'CID000041317': ['D05BB02'],\n",
       " 'CID000041684': ['P01AX11'],\n",
       " 'CID000041693': ['N01AH03'],\n",
       " 'CID000041744': ['L01DB09'],\n",
       " 'CID000041774': ['A10BF01'],\n",
       " 'CID000041781': ['C03CA04'],\n",
       " 'CID000042113': ['N01AB07'],\n",
       " 'CID000042615': ['L02AE01'],\n",
       " 'CID000042955': ['A02BB01', 'G02AD06'],\n",
       " 'CID000044564': ['S01GX05'],\n",
       " 'CID000047319': ['M03AC04'],\n",
       " 'CID000047320': ['M03AC11'],\n",
       " 'CID000047471': ['G01AF15'],\n",
       " 'CID000047528': ['C01DX16'],\n",
       " 'CID000047640': ['D01AE22'],\n",
       " 'CID000047725': ['L02AE03'],\n",
       " 'CID000050294': ['R03BC03', 'S01GX04', 'R01AC07'],\n",
       " 'CID000050614': ['J01DC05'],\n",
       " 'CID000051263': ['N01AH02'],\n",
       " 'CID000051577': ['A10BF02'],\n",
       " 'CID000051634': ['A16AX06'],\n",
       " 'CID000052421': ['D07AC18'],\n",
       " 'CID000054313': ['B01AC11'],\n",
       " 'CID000054373': ['H01CB02'],\n",
       " 'CID000054454': ['C10AA01'],\n",
       " 'CID000054547': ['J01DD13'],\n",
       " 'CID000054688': ['J01FA09'],\n",
       " 'CID000054786': ['B01AC21'],\n",
       " 'CID000054808': ['S01BA14'],\n",
       " 'CID000054840': ['N06BA09'],\n",
       " 'CID000055466': ['V08CA01'],\n",
       " 'CID000055480': ['N06AX17'],\n",
       " 'CID000056338': ['N03AB05'],\n",
       " 'CID000056959': ['C01EB18'],\n",
       " 'CID000057469': ['D06BB10'],\n",
       " 'CID000057537': ['N04BC09'],\n",
       " 'CID000059708': ['N03AX14'],\n",
       " 'CID000059768': ['C07AB09'],\n",
       " 'CID000060146': ['G04CA02'],\n",
       " 'CID000060164': ['D10AD03'],\n",
       " 'CID000060184': ['C09AA04'],\n",
       " 'CID000060198': ['L02BG06'],\n",
       " 'CID000060612': ['N05CM18'],\n",
       " 'CID000060613': ['J05AB12'],\n",
       " 'CID000060695': ['M03AC09'],\n",
       " 'CID000060706': ['J01DH02'],\n",
       " 'CID000060714': ['V08CA04'],\n",
       " 'CID000060726': ['S01BC11'],\n",
       " 'CID000060751': ['L01XD05'],\n",
       " 'CID000060752': ['C01BD05'],\n",
       " 'CID000060754': ['V08CA03'],\n",
       " 'CID000060787': ['J05AE01'],\n",
       " 'CID000060795': ['N05AX12'],\n",
       " 'CID000060814': ['N01AH06'],\n",
       " 'CID000060830': ['R03BB04'],\n",
       " 'CID000060834': ['N06AX21'],\n",
       " 'CID000060843': ['L01BA04'],\n",
       " 'CID000060852': ['M05BA06'],\n",
       " 'CID000060853': ['N05AE04'],\n",
       " 'CID000060864': ['R01AC08', 'S01GX09'],\n",
       " 'CID000060867': ['C01CX08'],\n",
       " 'CID000060871': ['J05AF08'],\n",
       " 'CID000060877': ['J05AF09'],\n",
       " 'CID000060878': ['C09CA02'],\n",
       " 'CID000060936': ['M05BA05'],\n",
       " 'CID000060953': ['L01BC06'],\n",
       " 'CID000062816': ['C10AC02'],\n",
       " 'CID000062819': ['G03GA04'],\n",
       " 'CID000062924': ['D07AC17', 'R01AD08', 'R03BA05'],\n",
       " 'CID000062959': ['J01MA13'],\n",
       " 'CID000064147': ['J05AB14'],\n",
       " 'CID000065014': ['L03AX16'],\n",
       " 'CID000065281': ['D06AX05', 'J01XX10', 'R02AB04'],\n",
       " 'CID000065628': ['L01AA09'],\n",
       " 'CID000065856': ['N06AX18'],\n",
       " 'CID000065863': ['D01AC14'],\n",
       " 'CID000065866': ['C08CA13'],\n",
       " 'CID000065999': ['C09CA07'],\n",
       " 'CID000068613': ['G02CX01'],\n",
       " 'CID000068740': ['M05BA08'],\n",
       " 'CID000068844': ['S01EC04'],\n",
       " 'CID000071158': ['N07BB03'],\n",
       " 'CID000071273': ['N01BB09'],\n",
       " 'CID000071301': ['C07AB12'],\n",
       " 'CID000071316': ['M03AC10'],\n",
       " 'CID000071329': ['C01BD04'],\n",
       " 'CID000071348': ['H01CB03'],\n",
       " 'CID000071362': ['C01EB19'],\n",
       " 'CID000071406': ['A11HA08'],\n",
       " 'CID000071616': ['J02AC03'],\n",
       " 'CID000072054': ['G04BD10'],\n",
       " 'CID000072081': ['H01BA04'],\n",
       " 'CID000072111': ['D06AX11', 'A07AA11'],\n",
       " 'CID000072466': ['L01DC01'],\n",
       " 'CID000072938': ['J01MA15'],\n",
       " 'CID000073303': ['J01DH04'],\n",
       " 'CID000073658': ['J01FA15'],\n",
       " 'CID000074989': ['P01AX06'],\n",
       " 'CID000077992': ['N02CC07'],\n",
       " 'CID000077993': ['N02CC06'],\n",
       " 'CID000077996': ['H05BX02'],\n",
       " 'CID000077998': ['A10BG02'],\n",
       " 'CID000082146': ['L01XX25'],\n",
       " 'CID000082148': ['N06AX22'],\n",
       " 'CID000083030': ['L04AA02'],\n",
       " 'CID000093860': ['L01XX32'],\n",
       " 'CID000096312': ['L01BB07'],\n",
       " 'CID000102258': ['M04AX01', 'V03AF07'],\n",
       " 'CID000102399': ['B05AA05'],\n",
       " 'CID000104741': ['L02BA03'],\n",
       " 'CID000104758': ['L01BA03'],\n",
       " 'CID000104799': ['L01AD05'],\n",
       " 'CID000104849': ['A08AX01'],\n",
       " 'CID000104865': ['C02KX01'],\n",
       " 'CID000110634': ['G04BE09'],\n",
       " 'CID000110635': ['G04BE08'],\n",
       " 'CID000114709': ['N03AF04'],\n",
       " 'CID000115237': ['N05AX13'],\n",
       " 'CID000115355': ['A16AX04'],\n",
       " 'CID000119182': ['L01BB06'],\n",
       " 'CID000119607': ['M01AH03'],\n",
       " 'CID000119828': ['M01AH04'],\n",
       " 'CID000119830': ['J05AF07'],\n",
       " 'CID000121396': ['A16AA05'],\n",
       " 'CID000121749': ['L04AC05'],\n",
       " 'CID000122197': ['V09AB03'],\n",
       " 'CID000122316': ['N04BD02'],\n",
       " 'CID000123015': ['B02BX04'],\n",
       " 'CID000123597': ['S01EE04'],\n",
       " 'CID000123606': ['N02CC05'],\n",
       " 'CID000123610': ['B01AC16'],\n",
       " 'CID000123611': ['B01AX05'],\n",
       " 'CID000123619': ['M01AH05'],\n",
       " 'CID000123620': ['R01AD09', 'R03BA07', 'D07XC03', 'D07AC13'],\n",
       " 'CID000123623': ['J02AX04'],\n",
       " 'CID000123631': ['L01XE02'],\n",
       " 'CID000124087': ['R06AX27'],\n",
       " 'CID000125017': ['N06AX23'],\n",
       " 'CID000125889': ['N03AX16'],\n",
       " 'CID000127909': ['S01EE03'],\n",
       " 'CID000129228': ['N03AF03'],\n",
       " 'CID000129806': ['C10AA07'],\n",
       " 'CID000130881': ['C09CA08'],\n",
       " 'CID000131535': ['J05AE07'],\n",
       " 'CID000132804': ['C10AA08'],\n",
       " 'CID000132999': ['C01EB17'],\n",
       " 'CID000134018': ['M04AA03'],\n",
       " 'CID000135113': ['A16AB06'],\n",
       " 'CID000145068': ['R07AX01'],\n",
       " 'CID000147912': ['J02AC04'],\n",
       " 'CID000148121': ['L01BA05'],\n",
       " 'CID000148191': ['L01XE09'],\n",
       " 'CID000148192': ['J05AE08'],\n",
       " 'CID000148211': ['A04AA05'],\n",
       " 'CID000150310': ['C03DA04'],\n",
       " 'CID000150311': ['C10AX09'],\n",
       " 'CID000150610': ['J01DH03'],\n",
       " 'CID000151075': ['S01BC10'],\n",
       " 'CID000151165': ['A04AD12'],\n",
       " 'CID000151171': ['C03XA02'],\n",
       " 'CID000152945': ['G04CB02'],\n",
       " 'CID000153941': ['J05AF10'],\n",
       " 'CID000154058': ['G04BD08'],\n",
       " 'CID000154256': ['G03XC02'],\n",
       " 'CID000156326': ['N05AH05'],\n",
       " 'CID000156418': ['H05BX01'],\n",
       " 'CID000157688': ['L01CA05'],\n",
       " 'CID000157920': ['A06AX03'],\n",
       " 'CID000157921': ['L01XD03'],\n",
       " 'CID000160051': ['C10AC04'],\n",
       " 'CID000163296': ['J05AE09'],\n",
       " 'CID000166548': ['J02AX06'],\n",
       " 'CID000168625': ['V03AE01'],\n",
       " 'CID000168924': ['V03AE03'],\n",
       " 'CID000170361': ['N07BA03'],\n",
       " 'CID000176870': ['L01XE03'],\n",
       " 'CID000193962': ['J05AG04'],\n",
       " 'CID000197281': ['V08CA08'],\n",
       " 'CID000197712': ['C02KX02'],\n",
       " 'CID000208902': ['N05CH02'],\n",
       " 'CID000208908': ['L01XE07'],\n",
       " 'CID000213023': ['B01AE07'],\n",
       " 'CID000213039': ['J05AE10'],\n",
       " 'CID000216209': ['C09XA02'],\n",
       " 'CID000216235': ['C02KX03'],\n",
       " 'CID000216237': ['C03XA01'],\n",
       " 'CID000216239': ['L01XE05'],\n",
       " 'CID000216326': ['L04AX04'],\n",
       " 'CID000219024': ['C01EB21'],\n",
       " 'CID000219078': ['N03AX18'],\n",
       " 'CID000219084': ['V08CA10'],\n",
       " 'CID000222786': ['H02AB10', 'S01BA03'],\n",
       " 'CID000358641': ['J01EE01'],\n",
       " 'CID000441332': ['G02AD04'],\n",
       " 'CID000441382': ['A07AA03', 'G01AA02', 'A01AB10', 'D01AA02', 'S01AA10'],\n",
       " 'CID000444006': ['J01DD16'],\n",
       " 'CID000444013': ['V08CA06'],\n",
       " 'CID000444033': ['R03BA08'],\n",
       " 'CID000449193': ['R03DX07'],\n",
       " 'CID000477468': ['J02AX05'],\n",
       " 'CID000483407': ['J05AX09'],\n",
       " 'CID000517045': ['B05XA08'],\n",
       " ...}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"http://sideeffects.embl.de/download/\"\"\"\n",
    "stitch_2_atc = pd.read_csv(\n",
    "    os.path.join(resource_directory, \"raw/DDI/drug_atc.tsv\"),\n",
    "    sep=\"\\t\",\n",
    "    header=None,\n",
    "    names=[\"STITCH\", \"ATC\"],\n",
    ")\n",
    "# http://stitch.embl.de/download/README\n",
    "stitch_2_atc.STITCH = stitch_2_atc.STITCH.str.replace(\"CID1\", \"CID0\")\n",
    "stitch_2_atc = stitch_2_atc.groupby(\"STITCH\").ATC.agg(lambda x: list(set(x))).to_dict()\n",
    "stitch_2_atc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T23:17:22.627301Z",
     "start_time": "2022-10-26T23:17:22.287818Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.9566012344279667\n",
      "0.8617562842500851\n"
     ]
    }
   ],
   "source": [
    "print(ddi[\"STITCH 1\"].isin(stitch_2_atc).mean())\n",
    "print(ddi[\"STITCH 2\"].isin(stitch_2_atc).mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T23:18:37.327342Z",
     "start_time": "2022-10-26T23:18:36.785180Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1011, 2)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ATC i</th>\n",
       "      <th>ATC j</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N03AE01</td>\n",
       "      <td>C03AA03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>J05AG03</td>\n",
       "      <td>J05AF05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N01AX10</td>\n",
       "      <td>J02AC03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>J02AC02</td>\n",
       "      <td>N01AX10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N05AD01</td>\n",
       "      <td>N06AX16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ATC i    ATC j\n",
       "0  N03AE01  C03AA03\n",
       "1  J05AG03  J05AF05\n",
       "2  N01AX10  J02AC03\n",
       "3  J02AC02  N01AX10\n",
       "4  N05AD01  N06AX16"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# DDI from paepr: GAMENet: Graph Augmented MEmory Networks for Recommending Medication Combination\n",
    "k = 40\n",
    "bottom_k_se = ddi[\"Polypharmacy Side Effect\"].value_counts().iloc[-k:].index\n",
    "ddi_bottom_k = ddi[ddi[\"Polypharmacy Side Effect\"].isin(bottom_k_se)]\n",
    "ddi_bottom_k = (\n",
    "    ddi_bottom_k[[\"STITCH 1\", \"STITCH 2\"]].drop_duplicates().reset_index(drop=True)\n",
    ")\n",
    "ddi_bottom_k[\"STITCH 1\"] = ddi_bottom_k[\"STITCH 1\"].map(stitch_2_atc)\n",
    "ddi_bottom_k[\"STITCH 2\"] = ddi_bottom_k[\"STITCH 2\"].map(stitch_2_atc)\n",
    "ddi_bottom_k = (\n",
    "    ddi_bottom_k.dropna()\n",
    "    .explode(\"STITCH 1\")\n",
    "    .explode(\"STITCH 2\")\n",
    "    .drop_duplicates()\n",
    "    .reset_index(drop=True)\n",
    ")\n",
    "ddi_bottom_k.columns = [\"ATC i\", \"ATC j\"]\n",
    "ddi_bottom_k.to_csv(\n",
    "    os.path.join(resource_directory, \"processed/DDI_GAMENet.csv\"), index=False\n",
    ")\n",
    "print(ddi_bottom_k.shape)\n",
    "ddi_bottom_k.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-26T23:18:48.211641Z",
     "start_time": "2022-10-26T23:18:47.382207Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(132662, 2)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ATC i</th>\n",
       "      <th>ATC j</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S01AA19</td>\n",
       "      <td>N01AH01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S01AA19</td>\n",
       "      <td>N02AB03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>J01CA01</td>\n",
       "      <td>N01AH01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>J01CA01</td>\n",
       "      <td>N02AB03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N01AB08</td>\n",
       "      <td>R03DA05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ATC i    ATC j\n",
       "0  S01AA19  N01AH01\n",
       "1  S01AA19  N02AB03\n",
       "2  J01CA01  N01AH01\n",
       "3  J01CA01  N02AB03\n",
       "4  N01AB08  R03DA05"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ddi_all = ddi[[\"STITCH 1\", \"STITCH 2\"]].drop_duplicates().reset_index(drop=True)\n",
    "ddi_all[\"STITCH 1\"] = ddi_all[\"STITCH 1\"].map(stitch_2_atc)\n",
    "ddi_all[\"STITCH 2\"] = ddi_all[\"STITCH 2\"].map(stitch_2_atc)\n",
    "ddi_all = (\n",
    "    ddi_all.dropna()\n",
    "    .explode(\"STITCH 1\")\n",
    "    .explode(\"STITCH 2\")\n",
    "    .drop_duplicates()\n",
    "    .reset_index(drop=True)\n",
    ")\n",
    "ddi_all.columns = [\"ATC i\", \"ATC j\"]\n",
    "ddi_all.to_csv(os.path.join(resource_directory, \"processed/DDI.csv\"), index=False)\n",
    "print(ddi_all.shape)\n",
    "ddi_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.13 ('kgc')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "217.796875px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "vscode": {
   "interpreter": {
    "hash": "3d0509d9aa81f2882b18eeb72d4d23c32cae9029e9b99f63cde94ba86c35ac78"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
